From: Scott Carey <scott@richrelevance.com>
To: Claudio Freire <klaussfreire@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
CC: Josh Berkus <josh@agliodbs.com>, postgres performance list
	<pgsql-performance@postgresql.org>
Date: Fri, 25 Mar 2011 10:55:08 -0700
Subject: Re: Shouldn't we have a way to avoid "risky" plans?
Thread-Topic: [PERFORM] Shouldn't we have a way to avoid "risky" plans?
Thread-Index: AcvrFWPQcQRxHqnkRX2ylc4wyBJDeQ==
Message-ID: <C9B21E17.2D11C%scott@richrelevance.com>
In-Reply-To: <AANLkTimD174+muSzfg4FGfat26V2ebEsDw7f9sLcwBR7@mail.gmail.com>
Accept-Language: en-US
Content-Language: en-US
user-agent: Microsoft-MacOutlook/14.2.0.101115
acceptlanguage: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0


On 3/23/11 2:08 PM, "Claudio Freire" <klaussfreire@gmail.com> wrote:

>On Wed, Mar 23, 2011 at 6:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Claudio Freire <klaussfreire@gmail.com> writes:
>>> In my head, safer =3D better worst-case performance.
>>
>> If the planner starts operating on the basis of worst case rather than
>> expected-case performance, the complaints will be far more numerous than
>> they are today.
>
>I imagine, that's why, if you put my comment in context, I was talking
>about picking a safer plan only when the "better on average one" fails
>miserably.

Postgres' assumption about what is 'better on average' is wrong in the
presence of nonlinear relationships between various statistics and
execution time anyway.

AVG(f(x)) !=3D f(AVG(x))

In english, the fastest plan for the average (most likely) case is not
always the fastest plan on average.  It works very well for many cases,
but falls down in others.

Many of the 'why is this query slow' and 'I wish there were hints'
problems I see here that are not user error seem related to this.  The
approaches discussed by Nathan Boley and Claudio Freire in this thread
could significantly mitigate many of the issues I have seen when wrestling
with the planner.

>
>--=20
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance