From: Scott Carey <scott@richrelevance.com>
To: Claudio Freire <klaussfreire@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Shouldn't we have a way to avoid "risky" plans?
Date: Fri, 25 Mar 2011 10:55:08 -0700
Message-ID: <C9B21E17.2D11C%scott@richrelevance.com> (raw)
In-Reply-To: <AANLkTimD174+muSzfg4FGfat26V2ebEsDw7f9sLcwBR7@mail.gmail.com>
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 = 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)) != 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.
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
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: scott@richrelevance.com, klaussfreire@gmail.com, tgl@sss.pgh.pa.us, josh@agliodbs.com
Subject: Re: Shouldn't we have a way to avoid "risky" plans?
In-Reply-To: <C9B21E17.2D11C%scott@richrelevance.com>
* 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