Skip site navigation (1) Skip section navigation (2)

prepared query performs much worse than regular query

From: Richard Yen <dba(at)richyen(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: prepared query performs much worse than regular query
Date: 2010-05-21 22:53:41
Message-ID: 0E6DA13D-D053-4CB8-A301-DC3607BBF146@richyen.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Rosser SchwarzDate: 2010-05-22 00:30:23
Subject: Re: prepared query performs much worse than regular query
Previous:From: Stephen FrostDate: 2010-05-21 19:17:50
Subject: Re: Optimize date query for large child tables: GiST orGIN?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group