The following bug has been logged online:
Bug reference: 1334
Logged by: A. Steinmetz
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.4.6
Operating system: Linux
Description: PREPARE creates bad execution plan (40x slower)
Details:
Direct excution of:
explain analyze INSERT INTO results SELECT
pagesearch.weight,pagesearch.pageid FROM pagesearch,topictrace WHERE
pagesearch.wordid=924375 AND pagesearch.catid=topictrace.catid AND
topictrace.refid=1 LIMIT 1500;
gives:
Subquery Scan "*SELECT*" (cost=0.00..11348.27 rows=1500 width=8) (actual
time=0.317..44.297 rows=1500 loops=1)
-> Limit (cost=0.00..11333.27 rows=1500 width=8) (actual
time=0.314..42.909 rows=1500 loops=1)
-> Nested Loop (cost=0.00..40202.90 rows=5321 width=8) (actual
time=0.311..42.185 rows=1500 loops=1)
-> Index Scan using pgscwdidx on pagesearch
(cost=0.00..173.32rows=7580 width=12) (actual time=0.167..2.725 rows=1500
loops=1)
Index Cond: (wordid = 924375)
-> Index Scan using tptrc on topictrace (cost=0.00..5.27
rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1500)
Index Cond: ((topictrace.refid = 1) AND ("outer".catid
= topictrace.catid))
Total runtime: 53.663 ms
(8 rows)
======================================================
Now, executing:
prepare t1 (integer,integer) as INSERT INTO results SELECT
pagesearch.weight,pagesearch.pageid FROM pagesearch,topictrace WHERE
pagesearch.wordid=$1 AND pagesearch.catid=topictrace.catid AND
topictrace.refid=$2 LIMIT 1500;
explain analyze execute t1 (924375,1);
gives:
Subquery Scan "*SELECT*" (cost=6569.10..6619.22 rows=17 width=8) (actual
time=2013.509..2039.757 rows=1500 loops=1)
-> Limit (cost=6569.10..6619.05 rows=17 width=8) (actual
time=2013.503..2038.543 rows=1500 loops=1)
-> Merge Join (cost=6569.10..6619.05 rows=17 width=8) (actual
time=2013.500..2037.904 rows=1500 loops=1)
Merge Cond: ("outer".catid = "inner".catid)
-> Sort (cost=701.29..721.28 rows=7996 width=12) (actual
time=32.194..32.546 rows=1500 loops=1)
Sort Key: pagesearch.catid
-> Index Scan using pgscwdidx on pagesearch
(cost=0.00..182.94 rows=7996 width=12) (actual time=0.176..15.574 rows=9267
loops=1)
Index Cond: (wordid = $1)
-> Sort (cost=5867.81..5872.71 rows=1960 width=4) (actual
time=1981.179..1988.281 rows=31483 loops=1)
Sort Key: topictrace.catid
-> Index Scan using tptrc on topictrace
(cost=0.00..5760.63 rows=1960 width=4) (actual time=0.172..978.313
rows=650273 loops=1)
Index Cond: (refid = $2)
Total runtime: 2155.218 ms
(13 rows)
=====================================================
This means that using a prepared statement instead of a direct query is *40*
times slower!
Some more information about the tables used:
CREATE TEMPORARY TABLE results (weight INTEGER,pageid INTEGER);
CREATE INDEX residx ON results (weight);
CREATE TABLE pagesearch (serial INTEGER PRIMARY KEY,wordid INTEGER,weight
INTEGER,pageid INTEGER,catid INTEGER,ages INTEGER);
CREATE INDEX pgscwdidx on pagesearch (wordid);
CREATE TABLE topictrace (serial INTEGER PRIMARY KEY,refid INTEGER,catid
INTEGER);
CREATE INDEX tptrc on topictrace (refid,catid);
Data volumes in the non-temporary tables:
pagesearch: 48318888 rows
topictrace: 5271657 rows
Note: the same prepared statement works well with other typical databases
(e.g. MySQL, SQLite).
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
