diff options
author | Yorhel <git@yorhel.nl> | 2010-11-14 10:29:05 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2010-11-14 10:30:53 +0100 |
commit | a0dff7f8d6ad1aeb508fd1bfb5f7bdbcbf16243a (patch) | |
tree | ec844e96b00ac61e336ae4f25ca0d7da334cb55a /util/sql | |
parent | 9a3e82df5241ebcba366a7a53c87b09defe9e156 (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.sql | 4 |
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; |