BUG #1334: PREPARE creates bad execution plan (40x slower) - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      1334
Logged by:          A. Steinmetz

Email address:      ast@domdv.de

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).