summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2010-12-13 21:26:39 +0100
committerYorhel <git@yorhel.nl>2010-12-13 21:34:15 +0100
commitd09b88b13fe90d15071c31bdeb5c9c69bceb0bc1 (patch)
treea26e0166c865afec8341468d194796375ae41528 /util
parentd63d8828315b056c41a64697aaf118551c052770 (diff)
SQL: Added index on tags_vn.date
This index speeds up most of the reports generated using /g/links. I've also tested other index possibilities, but they weren't all that satisfactionary: - An index on tags_vn.uid does speed up several queries, but since these queries may not have to be very common, I'd like to have some more real-world testing before adding the index. - Sorting on VN title is slow, regardless of any indexes on vn_rev.title or tags_vn.vid (not tested one on vn.latest, which could possibly help...). I have seen a few queries that were faster with an index on tags_vn.vid, but not many and not significantly. - An index on vn_rev.tag isn't necessary, the index for the primary key on (tag,vid,uid) already takes care of that. PostgreSQL is clever enough to be able to use that multi-column index for the tag column.
Diffstat (limited to 'util')
-rw-r--r--util/updates/update_2.15.sql3
1 files changed, 3 insertions, 0 deletions
diff --git a/util/updates/update_2.15.sql b/util/updates/update_2.15.sql
index b35b7636..75961785 100644
--- a/util/updates/update_2.15.sql
+++ b/util/updates/update_2.15.sql
@@ -2,3 +2,6 @@
ALTER TABLE tags_vn ADD COLUMN date timestamptz NOT NULL DEFAULT NOW();
+-- this index is essential, quite often sorted on
+CREATE INDEX tags_vn_date ON tags_vn (date);
+