From: Guillaume Cottenceau <gc@mnc.ch>
To: Mario Splivalo <mario.splivalo@megafon.hr>
To: pgsql-performance@postgresql.org
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [PERFORM] Query much slower when run from postgres function
Date: Mon, 09 Mar 2009 17:51:24 +0100
Message-ID: <87fxhmmygj.fsf@meuh.mnc.lan> (raw)
In-Reply-To: <27649.1236616294@sss.pgh.pa.us>
References: <49B51B60.6030600@megafon.hr>
<27649.1236616294@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gc@mnc.ch, mario.splivalo@megafon.hr, pgsql-jdbc@postgresql.org
Subject: Re: [PERFORM] Query much slower when run from postgres function
In-Reply-To: <87fxhmmygj.fsf@meuh.mnc.lan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox