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/dump.sql | |
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/dump.sql')
-rw-r--r-- | util/dump.sql | 4 |
1 files changed, 2 insertions, 2 deletions
diff --git a/util/dump.sql b/util/dump.sql index bf13c115..cf8821ec 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -494,7 +494,7 @@ $$ LANGUAGE plpgsql; -- recalculate tags_vn_inherit 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 @@ -524,7 +524,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; |