Re: [PERFORM] Query much slower when run from postgres function

Список
Период
Сортировка
От Guillaume Cottenceau
Тема Re: [PERFORM] Query much slower when run from postgres function
Дата
Msg-id 87fxhmmygj.fsf@meuh.mnc.lan
обсуждение исходный текст
Ответы Re: [PERFORM] Query much slower when run from postgres function  (Guillaume Smet)
Re: [PERFORM] Query much slower when run from postgres function  (Mario Splivalo)
Список pgsql-jdbc
Дерево обсуждения
Re: [PERFORM] Query much slower when run from postgres function  (Guillaume Cottenceau, )
 Re: [PERFORM] Query much slower when run from postgres function  (Guillaume Smet, )
  Re: [PERFORM] Query much slower when run from postgres function  (Tom Lane, )
   Re: [PERFORM] Query much slower when run from postgres function  (Andreas Wenk, )
   Re: [PERFORM] Query much slower when run from postgres function  (Dave Cramer, )
    Re: [PERFORM] Query much slower when run from postgres function  (James Mansion, )
    Re: [PERFORM] Query much slower when run from postgres function  (Oliver Jowett, )
     Re: [PERFORM] Query much slower when run from postgres function  (Guillaume Cottenceau, )
      Re: [PERFORM] Query much slower when run from postgres function  (Oliver Jowett, )
       Re: [PERFORM] Query much slower when run from postgres function  (Guillaume Cottenceau, )
        Re: [PERFORM] Query much slower when run from postgres function  (Kris Jurka, )
  Re: [PERFORM] Query much slower when run from postgres function  (Scott Carey, )
   Re: [PERFORM] Query much slower when run from postgres function  (Oliver Jowett, )
    Re: [PERFORM] Query much slower when run from postgres function  (Scott Carey, )
 Re: [PERFORM] Query much slower when run from postgres function  (Mario Splivalo, )
Tom Lane <tgl 'at' sss.pgh.pa.us> writes:

> Mario Splivalo <> 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

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Kris Jurka
Дата:
Сообщение: Re: getGeneratedKeys
Следующее
От: "Dickson S. Guedes"
Дата:
Сообщение: Renaming sequence auto generated by SERIAL type don't update pg_attrdef