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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

  From Date Subject
Next Message Rosser Schwarz 2010-05-22 00:30:23 Re: prepared query performs much worse than regular query
Previous Message Stephen Frost 2010-05-21 19:17:50 Re: Optimize date query for large child tables: GiST or GIN?