summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-11-29 19:03:42 +0100
committerYorhel <git@yorhel.nl>2009-11-29 19:09:36 +0100
commitfbcadd3ceb50a97dab4dd5cd284cc35e8b89b3b4 (patch)
tree99996735dd5878780bdf9c2422efae3c8cc6459a
parent11290678d9b900baec7a92581a6f87cc70d329ea (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.
-rw-r--r--lib/VNDB/DB/VN.pm1
-rw-r--r--util/dump.sql4
-rw-r--r--util/updates/update_2.10.sql4
3 files changed, 5 insertions, 4 deletions
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index feaa9bd0..8bd7b0f8 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -93,6 +93,7 @@ sub dbVNGet {
'(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS p_ranking',
'(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > v.c_rating) AS r_ranking',
) : (),
+ # TODO: optimize this, as it will be very slow when the selected tags match a lot of VNs (>1000)
$tag_ids ?
qq|(SELECT AVG(tvh.rating) FROM tags_vn_inherit tvh WHERE tvh.tag IN($tag_ids) AND tvh.vid = v.id AND spoiler <= $o{tags_include}[0] GROUP BY tvh.vid) AS tagscore| : (),
);
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;
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;