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

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.

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