diff options
author | Yorhel <git@yorhel.nl> | 2009-11-29 19:03:42 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-11-29 19:09:36 +0100 |
commit | fbcadd3ceb50a97dab4dd5cd284cc35e8b89b3b4 (patch) | |
tree | 99996735dd5878780bdf9c2422efae3c8cc6459a /util/updates | |
parent | 11290678d9b900baec7a92581a6f87cc70d329ea (diff) |
SQL: Replace index on tags_vn_inherit (tag) with an index on (tag,vid)
This drastically improves the performance of the search-VN-tag-filter
feature, and it seems PostgreSQL can use the index even when only
filtering results by the tag column.
Diffstat (limited to 'util/updates')
-rw-r--r-- | util/updates/update_2.10.sql | 4 |
1 files changed, 2 insertions, 2 deletions
diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql index 04b37bbf..469cce49 100644 --- a/util/updates/update_2.10.sql +++ b/util/updates/update_2.10.sql @@ -13,7 +13,7 @@ CREATE TABLE tags_vn_inherit ( -- more efficient version of tag_vn_calc() CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$ BEGIN - DROP INDEX IF EXISTS tags_vn_inherit_tag; + DROP INDEX IF EXISTS tags_vn_inherit_tag_vid; TRUNCATE tags_vn_inherit; -- populate tags_vn_inherit INSERT INTO tags_vn_inherit @@ -43,7 +43,7 @@ BEGIN GROUP BY tag, vid HAVING AVG(vote) > 0; -- recreate index - CREATE INDEX tags_vn_inherit_tag ON tags_vn_inherit (tag); + CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid); -- and update the VN count in the tags table UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); RETURN; |