On 10/20/2015 03:45 AM, Pavel Stehule wrote: > > > 2015-10-20 8:55 GMT+02:00 Thomas Kellerer <spam_eater@xxxxxxx > <mailto:spam_eater@xxxxxxx>>: > > 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. -- Jonathan Rogers Socialserve.com by Emphasys Software jrogers@xxxxxxxxxxxxxxxxxxxxx -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
