Re: SELECT slows down on sixth execution

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
>