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) >
