Re: Avoiding bad prepared-statement plans.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Bart Samwel <bart(at)samwel(dot)tk>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Jeroen Vermeulen <jtv(at)xs4all(dot)nl>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Avoiding bad prepared-statement plans.
Date: 2010-02-11 16:17:40
Message-ID: 10153.1265905060@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Feb 11, 2010 at 7:48 AM, Bart Samwel <bart(at)samwel(dot)tk> wrote:
>> Because that's the
>> underlying assumption of the "ratio" criterion -- that re-planning with
>> filled-in parameters takes about as much time as the initial planning run
>> took.

> We only want to replan when replanning is relatively cheap compared to
> execution,

Well, no, consider the situation where planning takes 50 ms, the generic
plan costs 100ms to execute, but a parameter-specific plan would take 1ms
to execute. Planning is very expensive compared to execution but it's
still a win to do it.

The problem that we face is that we don't have any very good way to tell
whether a fresh planning attempt is likely to yield a plan significantly
better than the generic plan. I can think of some heuristics --- for
example if the query contains LIKE with a parameterized pattern or a
partitioned table --- but that doesn't seem like a particularly nice
road to travel.

A possible scheme is to try it and keep track of whether we ever
actually do get a better plan. If, after N attempts, none of the custom
plans were ever more than X% cheaper than the generic one, then give up
and stop attempting to produce custom plans. Tuning the variables might
be challenging though.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2010-02-11 16:17:42 Re: [PATCH] Output configuration status after ./configure run.
Previous Message Robert Haas 2010-02-11 16:17:38 Re: [PATCH] Output configuration status after ./configure run.