path: root/util/dump.sql
diff options
authorYorhel <>2009-11-15 11:09:34 +0100
committerYorhel <>2009-11-15 11:14:06 +0100
commit06832f907632574ae41bf96e27e028b224078d39 (patch)
treec7d08887c0f05966fddf15548aeae48ada231680 /util/dump.sql
parent7f1b892cb46c8f713a3bb7c5ea94de2f7aa42508 (diff)
SQL: Improved performance of update_vnpopularity()
The previous statement was optimized for PostgreSQL 8.3 and took only about a second, but after the update to 8.4 it took about 10 times longer due to a different execution plan being generated. This slightly reworded statement generates a more efficient plan on 8.4.
Diffstat (limited to 'util/dump.sql')
1 files changed, 1 insertions, 1 deletions
diff --git a/util/dump.sql b/util/dump.sql
index a5451395..e0af79e8 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -481,7 +481,7 @@ BEGIN
SELECT v.uid, v.vid, sqrt(count(*))::real
FROM votes v
JOIN votes v2 ON v.uid = v2.uid AND <
- WHERE v.uid NOT IN(SELECT id FROM users WHERE ign_votes)
+ JOIN users u ON = v.uid AND NOT ign_votes
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;