2015-10-20 16:48 GMT+02:00 Jonathan Rogers <jrog...@socialserve.com>:
> On 10/20/2015 03:45 AM, Pavel Stehule wrote: > > > > > > 2015-10-20 8:55 GMT+02:00 Thomas Kellerer <spam_ea...@gmx.net > > <mailto:spam_ea...@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 > jrog...@emphasys-software.com >
