Hi everyone,
I use DBD::Pg to interface with our 8.4.2 database, but for a particular query,
performance is horrible. I'm assuming that the behavior of $dbh->prepare is as
if I did PREPARE foo AS (query), so I did an explain analyze in the commandline:
> db_alpha=# prepare foo6 as (SELECT me.id, me.assignment, me.title,
> me.x_firstname, me.x_lastname, me.owner, me.node, me.grade, me.folder,
> me.word_count, me.char_length, me.char_count, me.page_count FROM submissions
> me WHERE ( ( owner = $1 AND me.assignment = $2 ) ));
> PREPARE
> db_alpha=# explain analyze execute foo6('-1', '8996557');
> QUERY
> PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on submissions me (cost=38.84..42.85 rows=1 width=70)
> (actual time=346567.665..346567.665 rows=0 loops=1)
> Recheck Cond: ((assignment = $2) AND (owner = $1))
> -> BitmapAnd (cost=38.84..38.84 rows=1 width=0) (actual
> time=346567.642..346567.642 rows=0 loops=1)
> -> Bitmap Index Scan on submissions_assignment_idx
> (cost=0.00..19.27 rows=177 width=0) (actual time=0.038..0.038 rows=2 loops=1)
> Index Cond: (assignment = $2)
> -> Bitmap Index Scan on submissions_owner_idx (cost=0.00..19.32
> rows=184 width=0) (actual time=346566.501..346566.501 rows=28977245 loops=1)
> Index Cond: (owner = $1)
> Total runtime: 346567.757 ms
> (8 rows)
Now, if I run it without preparing it--just run it directly in the
commandline--I get this plan:
> db_alpha=# explain analyze SELECT me.id, me.assignment, me.title,
> me.x_firstname, me.x_lastname, me.owner, me.node, me.grade, me.folder,
> me.word_count, me.char_length, me.char_count, me.page_count FROM submissions
> me WHERE ( ( owner = -1 AND me.assignment = 8996557 ) )
> db_alpha-# ;
> QUERY
> PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using submissions_assignment_idx on submissions me
> (cost=0.00..549.15 rows=36 width=70) (actual time=0.021..0.021 rows=0 loops=1)
> Index Cond: (assignment = 8996557)
> Filter: (owner = (-1))
> Total runtime: 0.042 ms
> (4 rows)
submissions has ~124 million rows, and owner -1 is a placeholder in my
database, to fulfill a foreign key requirement. I tried REINDEXing
submissions_owner_idx and performing a VACUUM ANALYZE on the submissions table,
but nothing seems to make a difference for this query. One other thing to note
is that if I use any other value for the owner column, it comes back really
fast (< 0.04 ms).
Any ideas why the query planner chooses a different query plan when using
prepared statements?
--Richard
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
