summaryrefslogtreecommitdiff
path: root/util/sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2010-11-14 10:29:05 +0100
committerYorhel <git@yorhel.nl>2010-11-14 10:30:53 +0100
commita0dff7f8d6ad1aeb508fd1bfb5f7bdbcbf16243a (patch)
treeec844e96b00ac61e336ae4f25ca0d7da334cb55a /util/sql
parent9a3e82df5241ebcba366a7a53c87b09defe9e156 (diff)
Improved performance of update_vnpopularity() on PostgreSQL 9.0
Back from 180s to ~5 or ~6 seconds. It used to be quite fast on PostgreSQL 8.4, but somehow the planner failed to generate a proper execution plan for that query after the upgrade. This query is fast 9.0.
Diffstat (limited to 'util/sql')
-rw-r--r--util/sql/func.sql4
1 files changed, 3 insertions, 1 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 371e3ed0..c2a83d9c 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -59,6 +59,8 @@ $$ LANGUAGE sql;
-- recalculate vn.c_popularity
CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$
BEGIN
+ -- the following queries only update rows with popularity > 0, so make sure to reset all rows first
+ UPDATE vn SET c_popularity = NULL;
CREATE OR REPLACE TEMP VIEW tmp_pop1 (uid, vid, rank) AS
SELECT v.uid, v.vid, sqrt(count(*))::real
FROM votes v
@@ -67,7 +69,7 @@ BEGIN
GROUP BY v.vid, v.uid;
CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS
SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid;
- UPDATE vn SET c_popularity = (SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id);
+ UPDATE vn SET c_popularity = s1.win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 s1 WHERE s1.vid = vn.id;
RETURN;
END;
$$ LANGUAGE plpgsql;