[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] SELECT slows down on sixth execution
From: Pavel Stehule <pavel.stehule () gmail ! com>
Date: 2015-10-20 15:01:54
Message-ID: CAFj8pRDQD-GCz9EM7rfKvMqADxQTWz6tBPw-=QEif3OU8x6LuQ () mail ! gmail ! com
[Download RAW message or body]
2015-10-20 16:48 GMT+02:00 Jonathan Rogers <jrogers@socialserve.com>:
> On 10/20/2015 03:45 AM, Pavel Stehule wrote:
> >
> >
> > 2015-10-20 8:55 GMT+02:00 Thomas Kellerer <spam_eater@gmx.net
> > <mailto:spam_eater@gmx.net>>:
> >
> > Jonathan Rogers schrieb am 17.10.2015 um 04:14:
> > > > > Yes, I have been looking at both plans and can see where they
> > diverge.
> > > > > How could I go about figuring out why Postgres fails to see the
> > large
> > > > > difference in plan execution time? I use exactly the same
> parameters
> > > > > every time I execute the prepared statement, so how would
> > Postgres come
> > > > > to think that those are not the norm?
> > > >
> > > > PostgreSQL does not consider the actual query execution time, it
> only
> > > > compares its estimates for there general and the custom plan.
> > > > Also, it does not keep track of the parameter values you supply,
> > > > only of the average custom plan query cost estimate.
> > >
> > > OK, that makes more sense then. It's somewhat tedious for the
> > purpose of
> > > testing to execute a prepared statement six times to see the plan
> > which
> > > needs to be optimized. Unfortunately, there doesn't seem to be any
> way
> > > to force use of a generic plan in SQL based on Pavel Stehule's
> reply.
> >
> >
> > If you are using JDBC the threshold can be changed:
> >
> > https://jdbc.postgresql.org/documentation/94/server-prepare.html
> >
> >
> https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGStatement.html#setPrepareThreshold%28int%29
>
> >
> > As I don't think JDBC is using anything "exotic" I would be
> > surprised if this
> > can't be changed with other programming environments also.
> >
> >
> > This is some different - you can switch between server side prepared
> > statements and client side prepared statements in JDBC. It doesn't
> > change the behave of server side prepared statements in Postgres.
>
> I am using psycopg2 with a layer on top which can automatically PREPARE
> statements, so I guess that implements something similar to the JDBC
> interface. I did solve my problem by turning off the automatic preparation.
>
yes, you did off server side prepared statements.
Pavel
>
> --
> Jonathan Rogers
> Socialserve.com by Emphasys Software
> jrogers@emphasys-software.com
>
[Attachment #3 (text/html)]
<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">2015-10-20 \
16:48 GMT+02:00 Jonathan Rogers <span dir="ltr"><<a \
href="mailto:jrogers@socialserve.com" \
target="_blank">jrogers@socialserve.com</a>></span>:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><span class="">On 10/20/2015 03:45 AM, Pavel Stehule \
wrote:<br> ><br>
><br>
> 2015-10-20 8:55 GMT+02:00 Thomas Kellerer <<a \
href="mailto:spam_eater@gmx.net">spam_eater@gmx.net</a><br> </span>> <mailto:<a \
href="mailto:spam_eater@gmx.net">spam_eater@gmx.net</a>>>:<br> <div><div \
class="h5">><br> > Jonathan Rogers schrieb am 17.10.2015 um 04:14:<br>
> >>> Yes, I have been looking at both plans and can see where \
they<br> > diverge.<br>
> >>> How could I go about figuring out why Postgres fails to see \
the<br> > large<br>
> >>> difference in plan execution time? I use exactly the same \
parameters<br> > >>> every time I execute the prepared statement, \
so how would<br> > Postgres come<br>
> >>> to think that those are not the norm?<br>
> >><br>
> >> PostgreSQL does not consider the actual query execution time, it \
only<br> > >> compares its estimates for there general and the custom \
plan.<br> > >> Also, it does not keep track of the parameter values \
you supply,<br> > >> only of the average custom plan query cost \
estimate.<br> > ><br>
> > OK, that makes more sense then. It's somewhat tedious for \
the<br> > purpose of<br>
> > testing to execute a prepared statement six times to see the \
plan<br> > which<br>
> > needs to be optimized. Unfortunately, there doesn't seem to be \
any way<br> > > to force use of a generic plan in SQL based on Pavel \
Stehule's reply.<br> ><br>
><br>
> If you are using JDBC the threshold can be changed:<br>
><br>
> <a href="https://jdbc.postgresql.org/documentation/94/server-prepare.html" \
rel="noreferrer" target="_blank">https://jdbc.postgresql.org/documentation/94/server-prepare.html</a><br>
><br>
> <a href="https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGStatement.html#setPrepareThreshold%28int%29" \
rel="noreferrer" target="_blank">https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGStatement.html#setPrepareThreshold%28int%29</a><br>
><br>
> As I don't think JDBC is using anything "exotic" I would \
be<br> > surprised if this<br>
> can't be changed with other programming environments also.<br>
><br>
><br>
> This is some different - you can switch between server side prepared<br>
> statements and client side prepared statements in JDBC. It doesn't<br>
> change the behave of server side prepared statements in Postgres.<br>
<br>
</div></div>I am using psycopg2 with a layer on top which can automatically \
PREPARE<br> statements, so I guess that implements something similar to the JDBC<br>
interface. I did solve my problem by turning off the automatic \
preparation.<br></blockquote><div><br></div><div>yes, you did off server side \
prepared statements.<br><br></div><div>Pavel<br> <br></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"> <span class="HOEnZb"><font color="#888888"><br>
--<br>
Jonathan Rogers<br>
Socialserve.com by Emphasys Software<br>
<a href="mailto:jrogers@emphasys-software.com">jrogers@emphasys-software.com</a><br>
</font></span></blockquote></div><br></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic