To: Mario Splivalo <mario.splivalo@megafon.hr>,
	pgsql-performance@postgresql.org
cc: pgsql-jdbc@postgresql.org
Subject: Re: [PERFORM] Query much slower when run from postgres function
References: <49B51B60.6030600@megafon.hr> <27649.1236616294@sss.pgh.pa.us>
From: Guillaume Cottenceau <gc@mnc.ch>
Date: Mon, 09 Mar 2009 17:51:24 +0100
In-Reply-To: <27649.1236616294@sss.pgh.pa.us> (Tom Lane's message of "Mon\,
	09 Mar 2009 12\:31\:34 -0400")
Message-ID: <87fxhmmygj.fsf@meuh.mnc.lan>
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.3 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Tom Lane <tgl 'at' sss.pgh.pa.us> writes:

> Mario Splivalo <mario.splivalo@megafon.hr> 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