Tom Lane <tgl 'at' sss.pgh.pa.us> writes: > Mario Splivalo <mario.splivalo@xxxxxxxxxx> writes: >> Now I'm confused, why is 'sql' function much slower than 'direct' SELECT? > > Usually the reason for this is that the planner chooses a different plan > when it has knowledge of the particular value you are searching for than > when it does not. Yes, and since Mario is coming from JDBC, I'll share my part on this: I also noticed some very wrong plans in JDBC because of the "optimization" in prepared statements consisting of planning once for all runs, e.g. without any parameter values to help planning. My understanding is that practically, it's difficult for the planner to opt for an index (or not) because the selectivity of a parameter value may be much different when the actual value changes. Normally, the planner "thinks" that planning is so costly that it's better to plan once for all runs, but practically for our use, this is very wrong (it may be very good for some uses, though it would be interesting to know the actual uses share). Until it's possible to specifically tell the JDBC driver (and/or PG?) to not plan once for all runs (or is there something better to think of?), or the whole thing would be more clever (off the top of my head, PG could try to replan with the first actual values - or first xx actual values - and if the plan is different, then flag that prepared statement for replanning each time if the overall time estimate is different enough), I've opted to tell the JDBC driver to use the protocol version 2, as prepared statements were not so much prepared back then (IIRC parameter interpolation is performed in driver and the whole SQL query is passed each time, parsed, and planned) using protocolVersion=2 in the JDBC URL. So far it worked very well for us. -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
