Re: SELECT slows down on sixth execution

2015-10-20 8:55 GMT+02:00 Thomas Kellerer <spam_eater(at)gmx(dot)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.

Pavel

>
> Thomas
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>