Re: Getting rid of cheap-startup-cost paths earlier

On 22 May 2012 12:12, PostgreSQL - Hans-Jürgen Schönig
<postgres(at)cybertec(dot)at> wrote:
>
> On May 22, 2012, at 9:57 AM, Simon Riggs wrote:
>
>> On 22 May 2012 06:50, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>>> Currently, the planner keeps paths that appear to win on the grounds of
>>> either cheapest startup cost or cheapest total cost.  It suddenly struck
>>> me that in many simple cases (viz, those with no LIMIT, EXISTS, cursor
>>> fast-start preference, etc) we could know a-priori that cheapest startup
>>> cost is not going to be interesting, and hence immediately discard any
>>> path that doesn't win on total cost.
>>
>> My experience is that most people don't provide a LIMIT explicitly
>> even when they know that's the desired behaviour. That's because
>> either they simply don't understand that SQL can return lots of rows,
>> or SQL knowledge isn't enough, or worse that people don't even know
>> that specifying it would alter query plans.
>>
>> Regrettably the current planning of LIMIT clauses causes more problems
>> so in many cases these have been explicitly removed from SQL by
>> developers that know how many rows they wish to see.
>>
>> I would have proposed a default-LIMIT parameter before now, but for
>> that last point.
>
>
> this sounds like a total disaster to me ...
> why in the world should we have a default LIMIT parameter?

Its common for people to write SQL they want to have a fast response
to, yet to not specify that in their SQL code.

> i guess if somebody is not able to use LIMIT he should better not touch the DB.
> we clearly cannot fix incompetence by adding parameters.

Not sure I understand that argument. Many things are set by default in
parameters.

Oracle allows the user to choose whether plans return rows quickly, or
execute plans efficiently overall. That is a relevant user choice, not
a hint. We simply don't know that if LIMIT is absent whether the user
wishes fast or efficient plans. If they use LIMIT we know they want
fast plan.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services