[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] Prepared statements and suboptimal plans
From: Royce Ausburn <esapersona () royce ! id ! au>
Date: 2011-09-21 17:08:04
Message-ID: 5FFFC37E-DE1C-40F6-9510-BC595886FD26 () royce ! id ! au
[Download RAW message or body]
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)
>
[Attachment #3 (unknown)]
<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; \
-webkit-line-break: after-white-space; "><br> Sorry all - this was a duplicate from \
another of my addresses =( Thanks to all that have helped out on both \
threads.<div><br></div><div><br></div><div><br></div><div><br></div><div><br><div><div>On \
21/09/2011, at 8:44 AM, Royce Ausburn wrote:</div><br \
class="Apple-interchange-newline"><blockquote type="cite"><div style="word-wrap: \
break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Hi \
all,<div><br></div><div>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.</div><div><br></div><div>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? </div><div><br></div><div>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?</div><div><br></div><div>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.</div><div><br></div><div>Any tips would be \
appreciated,</div><div><br></div><div>--Royce<br> \
<br></div><div><br></div><div><div><div><font class="Apple-style-span" \
face="Courier">test=# PREPARE test (integer) as </font></div><div><font \
class="Apple-style-span" face="Courier"> select </font></div><div><font \
class="Apple-style-span" face="Courier"> \
sid, </font></div><div><font class="Apple-style-span" face="Courier"> \
role, </font></div><div><font class="Apple-style-span" \
face="Courier"> starttime::date, </font></div><div><font \
class="Apple-style-span" face="Courier"> nasid, \
importer, </font></div><div><font class="Apple-style-span" face="Courier"> \
max(eventbinding.biid) as biid, </font></div><div><font \
class="Apple-style-span" face="Courier"> sum(bytesin) as \
bytesin, </font></div><div><font class="Apple-style-span" face="Courier"> \
sum(bytesout) as bytesout, </font></div><div><font \
class="Apple-style-span" face="Courier"> sum(seconds) as \
seconds, </font></div><div><font class="Apple-style-span" face="Courier"> \
sum(coalesce(pages, 0)) as pages, </font></div><div><font \
class="Apple-style-span" face="Courier"> sum(coalesce(count, 0)) as \
count, </font></div><div><font class="Apple-style-span" face="Courier"> \
sum(coalesce(rate, 0.0)) as rate, </font></div><div><font \
class="Apple-style-span" face="Courier"> sum(coalesce(bytesSentRate, \
0.0)) as bytesSentRate, </font></div><div><font class="Apple-style-span" \
face="Courier"> sum(coalesce(bytesReceivedRate, 0.0)) as \
bytesReceivedRate, </font></div><div><font class="Apple-style-span" \
face="Courier"> count(*) as entries </font></div><div><font \
class="Apple-style-span" face="Courier"> from billingItem, eventBinding , fqun \
</font></div><div><font class="Apple-style-span" face="Courier"> where \
eventBinding.biid > $1 and eventBinding.biid = billingItem.biid and fqun.uid = \
eventBinding.uid </font></div><div><font class="Apple-style-span" \
face="Courier"> group by sid, starttime::date, nasid, importer, \
role;</font></div><div><font class="Apple-style-span" \
face="Courier">PREPARE</font></div><div><font class="Apple-style-span" \
face="Courier">test=# explain EXECUTE test(57205899);</font></div><div><font \
class="Apple-style-span" face="Courier"> \
\
\
QUERY PLAN \
\
\
</font></div><div><font class="Apple-style-span" \
face="Courier">----------------------------------------------------------------------- \
----------------------------------------------------------------</font></div><div><font \
class="Apple-style-span" face="Courier"> GroupAggregate \
(cost=12338998.78..13770049.38 rows=18465169 width=148)</font></div><div><font \
class="Apple-style-span" face="Courier"> -> Sort \
(cost=12338998.78..12385161.70 rows=18465169 width=148)</font></div><div><font \
class="Apple-style-span" face="Courier"> Sort Key: \
fqun.sid, ((billingitem.starttime)::date), billingitem.nasid, billingitem.importer, \
eventbinding.role</font></div><div><font class="Apple-style-span" \
face="Courier"> -> Hash Join \
(cost=1498473.48..7333418.55 rows=18465169 width=148)</font></div><div><font \
class="Apple-style-span" face="Courier"> \
Hash Cond: (eventbinding.uid = fqun.uid)</font></div><div><font \
class="Apple-style-span" face="Courier"> \
-> Hash Join (cost=1496916.06..6916394.83 rows=18465169 \
width=148)</font></div><div><font class="Apple-style-span" face="Courier"> \
Hash Cond: \
(billingitem.biid = eventbinding.biid)</font></div><div><font \
class="Apple-style-span" face="Courier"> \
-> Seq Scan on billingitem \
(cost=0.00..1433087.88 rows=56222688 width=142)</font></div><div><font \
class="Apple-style-span" face="Courier"> \
-> Hash (cost=1175939.45..1175939.45 \
rows=18465169 width=10)</font></div><div><font class="Apple-style-span" \
face="Courier"> \
-> Bitmap Heap Scan on eventbinding \
(cost=427409.84..1175939.45 rows=18465169 width=10)</font></div><div><font \
class="Apple-style-span" face="Courier"> \
Recheck \
Cond: (biid > $1)</font></div><div><font class="Apple-style-span" \
face="Courier"> \
-> Bitmap Index Scan on \
eventbinding_biid_uid_role_idx (cost=0.00..422793.55 rows=18465169 \
width=0)</font></div><div><font class="Apple-style-span" face="Courier"> \
\
Index Cond: (biid > \
$1)</font></div><div><font class="Apple-style-span" face="Courier"> \
-> Hash (cost=943.85..943.85 \
rows=49085 width=8)</font></div><div><font class="Apple-style-span" \
face="Courier"> \
-> Seq Scan on fqun (cost=0.00..943.85 rows=49085 \
width=8)</font></div><div><font class="Apple-style-span" face="Courier">(15 \
rows)</font></div></div><div><br></div><div><br></div><div><br></div><div><br></div><div>As \
a query on the psql command line:</div><div><br></div><div><div><font \
class="Apple-style-span" face="Courier">test=# explain </font></div><div><font \
class="Apple-style-span" face="Courier"> select </font></div><div><font \
class="Apple-style-span" face="Courier"> \
sid, </font></div><div><font class="Apple-style-span" face="Courier"> \
role, </font></div><div><font class="Apple-style-span" \
face="Courier"> starttime::date, </font></div><div><font \
class="Apple-style-span" face="Courier"> \
nasid, </font></div><div><font class="Apple-style-span" face="Courier"> \
importer, </font></div><div><font class="Apple-style-span" \
face="Courier"> max(eventbinding.biid) as \
biid, </font></div><div><font class="Apple-style-span" face="Courier"> \
sum(bytesin) as bytesin, </font></div><div><font class="Apple-style-span" \
face="Courier"> sum(bytesout) as bytesout, </font></div><div><font \
class="Apple-style-span" face="Courier"> sum(seconds) as \
seconds, </font></div><div><font class="Apple-style-span" face="Courier"> \
sum(coalesce(pages, 0)) as pages, </font></div><div><font \
class="Apple-style-span" face="Courier"> sum(coalesce(count, 0)) as \
count, </font></div><div><font class="Apple-style-span" face="Courier"> \
sum(coalesce(rate, 0.0)) as rate, </font></div><div><font \
class="Apple-style-span" face="Courier"> sum(coalesce(bytesSentRate, \
0.0)) as bytesSentRate, </font></div><div><font class="Apple-style-span" \
face="Courier"> sum(coalesce(bytesReceivedRate, 0.0)) as \
bytesReceivedRate, </font></div><div><font class="Apple-style-span" \
face="Courier"> count(*) as entries </font></div><div><font \
class="Apple-style-span" face="Courier"> from billingItem, eventBinding , fqun \
</font></div><div><font class="Apple-style-span" face="Courier"> where \
eventBinding.biid > 57205899 and eventBinding.biid = billingItem.biid and fqun.uid \
= eventBinding.uid </font></div><div><font class="Apple-style-span" \
face="Courier"> group by sid, starttime::date, nasid, importer, \
role;</font></div><div><font class="Apple-style-span" face="Courier"> \
\
\
QUERY PLAN \
\
</font></div><div><font \
class="Apple-style-span" \
face="Courier">--------------------------------------------------------------------------------------------------------------------</font></div><div><font \
class="Apple-style-span" face="Courier"> HashAggregate \
(cost=102496.80..102704.55 rows=5540 width=148)</font></div><div><font \
class="Apple-style-span" face="Courier"> -> Hash Join \
(cost=1697.13..102289.05 rows=5540 width=148)</font></div><div><font \
class="Apple-style-span" face="Courier"> Hash Cond: \
(eventbinding.uid = fqun.uid)</font></div><div><font class="Apple-style-span" \
face="Courier"> -> Nested Loop \
(cost=139.71..100606.99 rows=5540 width=148)</font></div><div><font \
class="Apple-style-span" face="Courier"> \
-> Bitmap Heap Scan on eventbinding \
(cost=139.71..20547.20 rows=5540 width=10)</font></div><div><font \
class="Apple-style-span" face="Courier"> \
Recheck Cond: (biid > \
57205899)</font></div><div><font class="Apple-style-span" face="Courier"> \
-> \
Bitmap Index Scan on eventbinding_biid_uid_role_idx (cost=0.00..138.33 \
rows=5540 width=0)</font></div><div><font class="Apple-style-span" \
face="Courier"> \
Index Cond: (biid > 57205899)</font></div><div><font \
class="Apple-style-span" face="Courier"> \
-> Index Scan using billingitem_db52003_pkey on billingitem \
(cost=0.00..14.44 rows=1 width=142)</font></div><div><font \
class="Apple-style-span" face="Courier"> \
Index Cond: (billingitem.biid = \
eventbinding.biid)</font></div><div><font class="Apple-style-span" \
face="Courier"> -> Hash \
(cost=943.85..943.85 rows=49085 width=8)</font></div><div><font \
class="Apple-style-span" face="Courier"> \
-> Seq Scan on fqun (cost=0.00..943.85 rows=49085 \
width=8)</font></div><div><font class="Apple-style-span" face="Courier">(12 \
rows)</font></div></div></div><div><br></div></div></blockquote></div><br></div></body></html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic