Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id C35D1B5DC09 for ; Tue, 20 Sep 2011 20:27:20 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 60999-09 for ; Tue, 20 Sep 2011 23:27:14 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from ix.inomial.com (mail.inomial.net [202.62.159.215]) by mail.postgresql.org (Postfix) with ESMTP id 4CCD6B5DC1B for ; Tue, 20 Sep 2011 20:27:12 -0300 (ADT) Received: from jadzia.lan (ip-151.29.92.203.VOCUS.net.au [203.92.29.151]) by ix.inomial.com (Postfix) with ESMTPA id 509612C1B37 for ; Wed, 21 Sep 2011 09:27:10 +1000 (EST) From: Royce Ausburn Content-Type: multipart/alternative; boundary="Apple-Mail=_41E07086-F63B-4FF6-8F92-F55518195262" Subject: Prepared statements and suboptimal plans Date: Wed, 21 Sep 2011 09:27:10 +1000 Message-Id: To: pgsql-performance Mime-Version: 1.0 (Apple Message framework v1244.3) X-Mailer: Apple Mail (2.1244.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201109/294 X-Sequence-Number: 45070 --Apple-Mail=_41E07086-F63B-4FF6-8F92-F55518195262 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=windows-1252 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=85 An alternative = might be to re-write the query and hope that the planner's general plan = is a bit closer to optimal=85 but are these my only options? =20 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=85 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=3D# PREPARE test (integer) as=20 select=20 sid,=20 role,=20 starttime::date,=20 nasid, importer,=20 max(eventbinding.biid) as biid,=20 sum(bytesin) as bytesin,=20 sum(bytesout) as bytesout, =20 sum(seconds) as seconds,=20 sum(coalesce(pages, 0)) as pages,=20 sum(coalesce(count, 0)) as count, =20 sum(coalesce(rate, 0.0)) as rate,=20 sum(coalesce(bytesSentRate, 0.0)) as bytesSentRate, =20 sum(coalesce(bytesReceivedRate, 0.0)) as bytesReceivedRate,=20 count(*) as entries =20 from billingItem, eventBinding , fqun =20 where eventBinding.biid > $1 and eventBinding.biid =3D = billingItem.biid and fqun.uid =3D eventBinding.uid =20 group by sid, starttime::date, nasid, importer, role; PREPARE test=3D# explain EXECUTE test(57205899); QUERY PLAN = =20 = --------------------------------------------------------------------------= ------------------------------------------------------------- GroupAggregate (cost=3D12338998.78..13770049.38 rows=3D18465169 = width=3D148) -> Sort (cost=3D12338998.78..12385161.70 rows=3D18465169 width=3D148)= Sort Key: fqun.sid, ((billingitem.starttime)::date), = billingitem.nasid, billingitem.importer, eventbinding.role -> Hash Join (cost=3D1498473.48..7333418.55 rows=3D18465169 = width=3D148) Hash Cond: (eventbinding.uid =3D fqun.uid) -> Hash Join (cost=3D1496916.06..6916394.83 = rows=3D18465169 width=3D148) Hash Cond: (billingitem.biid =3D eventbinding.biid) -> Seq Scan on billingitem (cost=3D0.00..1433087.88= rows=3D56222688 width=3D142) -> Hash (cost=3D1175939.45..1175939.45 = rows=3D18465169 width=3D10) -> Bitmap Heap Scan on eventbinding = (cost=3D427409.84..1175939.45 rows=3D18465169 width=3D10) Recheck Cond: (biid > $1) -> Bitmap Index Scan on = eventbinding_biid_uid_role_idx (cost=3D0.00..422793.55 rows=3D18465169 = width=3D0) Index Cond: (biid > $1) -> Hash (cost=3D943.85..943.85 rows=3D49085 width=3D8) -> Seq Scan on fqun (cost=3D0.00..943.85 = rows=3D49085 width=3D8) (15 rows) As a query on the psql command line: test=3D# explain=20 select=20 sid,=20 role,=20 starttime::date,=20 nasid,=20 importer,=20 max(eventbinding.biid) as biid,=20 sum(bytesin) as bytesin,=20 sum(bytesout) as bytesout, =20 sum(seconds) as seconds,=20 sum(coalesce(pages, 0)) as pages,=20 sum(coalesce(count, 0)) as count, =20 sum(coalesce(rate, 0.0)) as rate,=20 sum(coalesce(bytesSentRate, 0.0)) as bytesSentRate, =20 sum(coalesce(bytesReceivedRate, 0.0)) as bytesReceivedRate,=20 count(*) as entries =20 from billingItem, eventBinding , fqun =20 where eventBinding.biid > 57205899 and eventBinding.biid =3D = billingItem.biid and fqun.uid =3D eventBinding.uid =20 group by sid, starttime::date, nasid, importer, role; QUERY PLAN = =20 = --------------------------------------------------------------------------= ------------------------------------------ HashAggregate (cost=3D102496.80..102704.55 rows=3D5540 width=3D148) -> Hash Join (cost=3D1697.13..102289.05 rows=3D5540 width=3D148) Hash Cond: (eventbinding.uid =3D fqun.uid) -> Nested Loop (cost=3D139.71..100606.99 rows=3D5540 = width=3D148) -> Bitmap Heap Scan on eventbinding = (cost=3D139.71..20547.20 rows=3D5540 width=3D10) Recheck Cond: (biid > 57205899) -> Bitmap Index Scan on = eventbinding_biid_uid_role_idx (cost=3D0.00..138.33 rows=3D5540 = width=3D0) Index Cond: (biid > 57205899) -> Index Scan using billingitem_db52003_pkey on = billingitem (cost=3D0.00..14.44 rows=3D1 width=3D142) Index Cond: (billingitem.biid =3D = eventbinding.biid) -> Hash (cost=3D943.85..943.85 rows=3D49085 width=3D8) -> Seq Scan on fqun (cost=3D0.00..943.85 rows=3D49085 = width=3D8) (12 rows) --Apple-Mail=_41E07086-F63B-4FF6-8F92-F55518195262 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=windows-1252 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=85 An alternative might be to re-write the = query and hope that the planner's general plan is a bit closer to = optimal=85 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=85 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=3D# 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 =3D billingItem.biid and fqun.uid =3D eventBinding.uid =  
  group by sid, starttime::date, nasid, importer, = role;
PREPARE
test=3D# explain EXECUTE = test(57205899);
                =                     =                     =       QUERY PLAN           =                     =                     =             
---------------------------------------------------------= --------------------------------------------------------------------------= ----
 GroupAggregate =  (cost=3D12338998.78..13770049.38 rows=3D18465169 = width=3D148)
   ->  Sort =  (cost=3D12338998.78..12385161.70 rows=3D18465169 = width=3D148)
         Sort Key: fqun.sid, = ((billingitem.starttime)::date), billingitem.nasid, = billingitem.importer, eventbinding.role
        =  ->  Hash Join  (cost=3D1498473.48..7333418.55 = rows=3D18465169 width=3D148)
        =        Hash Cond: (eventbinding.uid =3D = fqun.uid)
              =  ->  Hash Join  (cost=3D1496916.06..6916394.83 = rows=3D18465169 width=3D148)
        =              Hash Cond: = (billingitem.biid =3D eventbinding.biid)
        =              ->  Seq Scan on = billingitem  (cost=3D0.00..1433087.88 rows=3D56222688 = width=3D142)
                =      ->  Hash  (cost=3D1175939.45..1175939.45 = rows=3D18465169 width=3D10)
        =                   =  ->  Bitmap Heap Scan on eventbinding =  (cost=3D427409.84..1175939.45 rows=3D18465169 = width=3D10)
                =                  Recheck = Cond: (biid > $1)
                =                  -> =  Bitmap Index Scan on eventbinding_biid_uid_role_idx =  (cost=3D0.00..422793.55 rows=3D18465169 = width=3D0)
                =                     =    Index Cond: (biid > $1)
        =        ->  Hash  (cost=3D943.85..943.85 = rows=3D49085 width=3D8)
                =      ->  Seq Scan on fqun =  (cost=3D0.00..943.85 rows=3D49085 width=3D8)
(15 = rows)



=
As a query on the psql command = line:

test=3D# 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 =3D billingItem.biid and fqun.uid =3D eventBinding.uid =  
  group by sid, starttime::date, nasid, importer, = role;
                =                     =                  QUERY PLAN =                     =                     =             
---------------------------------------------------------= -----------------------------------------------------------
 HashAggregate =  (cost=3D102496.80..102704.55 rows=3D5540 = width=3D148)
   ->  Hash Join =  (cost=3D1697.13..102289.05 rows=3D5540 = width=3D148)
         Hash Cond: = (eventbinding.uid =3D fqun.uid)
        =  ->  Nested Loop  (cost=3D139.71..100606.99 rows=3D5540 = width=3D148)
              =  ->  Bitmap Heap Scan on eventbinding =  (cost=3D139.71..20547.20 rows=3D5540 = width=3D10)
                =      Recheck Cond: (biid > = 57205899)
                =      ->  Bitmap Index Scan on = eventbinding_biid_uid_role_idx  (cost=3D0.00..138.33 rows=3D5540 = width=3D0)
                =            Index Cond: (biid > = 57205899)
              =  ->  Index Scan using billingitem_db52003_pkey on = billingitem  (cost=3D0.00..14.44 rows=3D1 = width=3D142)
                =      Index Cond: (billingitem.biid =3D = eventbinding.biid)
         ->  Hash =  (cost=3D943.85..943.85 rows=3D49085 = width=3D8)
              =  ->  Seq Scan on fqun  (cost=3D0.00..943.85 rows=3D49085= width=3D8)
(12 = rows)

= --Apple-Mail=_41E07086-F63B-4FF6-8F92-F55518195262--