diff options
author | Yorhel <git@yorhel.nl> | 2010-12-13 21:26:39 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2010-12-13 21:34:15 +0100 |
commit | d09b88b13fe90d15071c31bdeb5c9c69bceb0bc1 (patch) | |
tree | a26e0166c865afec8341468d194796375ae41528 /util | |
parent | d63d8828315b056c41a64697aaf118551c052770 (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.sql | 3 |
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); + |