Re: Prepared statements and suboptimal plans

Royce Ausburn <esapersona@xxxxxxxxxxx> · Thu, 22 Sep 2011 03:08:04 +1000

Sorry all - this was a duplicate from another of my addresses =(  Thanks to all that have helped out on both threads.

On 21/09/2011, at 8:44 AM, Royce Ausburn wrote:

Hi all,
It looks like I've been hit with this well known issue.  I have a complicated query that is intended to run every few minutes, I'm using JDBC's Connection.prepareStatement() mostly for nice parameterisation, but postgres produces a suboptimal plan due to its lack of information when the statement is prepared.

I've been following the mailing list for a few years and I've seen this topic come up a bit.  I've just done a quick google and I'm not quite sure how to fix this short of manually substituting my query parameters in to a query string -- avoiding prepared statements… An alternative might be to re-write the query and hope that the planner's general plan is a bit closer to optimal… but are these my only options?  

I notice that the non-prepared-statement (both below my sig) plan estimates 5500 rows output.  I think that's out by a factor of up to 100, suggesting that I might want to increase my statistics and re-analyse… but as I understand the prepared-statement problem, this probably won't help here.  Correct?

We've been worst hit by this query on an 8.3 site.  Another site is running 8.4.  Have there been improvements in this area recently?  Upgrading to 9.0 might be viable for us.

Any tips would be appreciated,

--Royce

test=# PREPARE test (integer) as 
  select 
    sid, 
    role, 
    starttime::date, 
    nasid, importer, 
    max(eventbinding.biid) as biid, 
    sum(bytesin) as bytesin, 
    sum(bytesout) as bytesout,  
    sum(seconds) as seconds, 
    sum(coalesce(pages, 0)) as pages, 
    sum(coalesce(count, 0)) as count,  
    sum(coalesce(rate, 0.0)) as rate, 
    sum(coalesce(bytesSentRate, 0.0)) as bytesSentRate,  
    sum(coalesce(bytesReceivedRate, 0.0)) as bytesReceivedRate, 
    count(*) as entries  
  from billingItem, eventBinding , fqun  
  where eventBinding.biid > $1 and eventBinding.biid = billingItem.biid and fqun.uid = eventBinding.uid  
  group by sid, starttime::date, nasid, importer, role;
PREPARE
test=# explain EXECUTE test(57205899);
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=12338998.78..13770049.38 rows=18465169 width=148)
   ->  Sort  (cost=12338998.78..12385161.70 rows=18465169 width=148)
         Sort Key: fqun.sid, ((billingitem.starttime)::date), billingitem.nasid, billingitem.importer, eventbinding.role
         ->  Hash Join  (cost=1498473.48..7333418.55 rows=18465169 width=148)
               Hash Cond: (eventbinding.uid = fqun.uid)
               ->  Hash Join  (cost=1496916.06..6916394.83 rows=18465169 width=148)
                     Hash Cond: (billingitem.biid = eventbinding.biid)
                     ->  Seq Scan on billingitem  (cost=0.00..1433087.88 rows=56222688 width=142)
                     ->  Hash  (cost=1175939.45..1175939.45 rows=18465169 width=10)
                           ->  Bitmap Heap Scan on eventbinding  (cost=427409.84..1175939.45 rows=18465169 width=10)
                                 Recheck Cond: (biid > $1)
                                 ->  Bitmap Index Scan on eventbinding_biid_uid_role_idx  (cost=0.00..422793.55 rows=18465169 width=0)
                                       Index Cond: (biid > $1)
               ->  Hash  (cost=943.85..943.85 rows=49085 width=8)
                     ->  Seq Scan on fqun  (cost=0.00..943.85 rows=49085 width=8)
(15 rows)

As a query on the psql command line:

test=# explain 
  select 
    sid, 
    role, 
    starttime::date, 
    nasid, 
    importer, 
    max(eventbinding.biid) as biid, 
    sum(bytesin) as bytesin, 
    sum(bytesout) as bytesout,  
    sum(seconds) as seconds, 
    sum(coalesce(pages, 0)) as pages, 
    sum(coalesce(count, 0)) as count,  
    sum(coalesce(rate, 0.0)) as rate, 
    sum(coalesce(bytesSentRate, 0.0)) as bytesSentRate,  
    sum(coalesce(bytesReceivedRate, 0.0)) as bytesReceivedRate, 
    count(*) as entries  
  from billingItem, eventBinding , fqun  
  where eventBinding.biid > 57205899 and eventBinding.biid = billingItem.biid and fqun.uid = eventBinding.uid  
  group by sid, starttime::date, nasid, importer, role;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=102496.80..102704.55 rows=5540 width=148)
   ->  Hash Join  (cost=1697.13..102289.05 rows=5540 width=148)
         Hash Cond: (eventbinding.uid = fqun.uid)
         ->  Nested Loop  (cost=139.71..100606.99 rows=5540 width=148)
               ->  Bitmap Heap Scan on eventbinding  (cost=139.71..20547.20 rows=5540 width=10)
                     Recheck Cond: (biid > 57205899)
                     ->  Bitmap Index Scan on eventbinding_biid_uid_role_idx  (cost=0.00..138.33 rows=5540 width=0)
                           Index Cond: (biid > 57205899)
               ->  Index Scan using billingitem_db52003_pkey on billingitem  (cost=0.00..14.44 rows=1 width=142)
                     Index Cond: (billingitem.biid = eventbinding.biid)
         ->  Hash  (cost=943.85..943.85 rows=49085 width=8)
               ->  Seq Scan on fqun  (cost=0.00..943.85 rows=49085 width=8)
(12 rows)