Re: [HACKERS] Solution for LIMIT cost estimation

At 12:13 PM 2/13/00 -0500, Tom Lane wrote:
>Chris Bitmead <chrisb(at)nimrod(dot)itg(dot)telstra(dot)com(dot)au> writes:
>> Don Baccus wrote:
>>> But ... that doesn't mean that some folks might not want to use
>>> it differently. What if LIMIT 2 were more efficient that COUNT(*)
>>> in order to determine if more than one row satisfies a condition?
>
>> select count(*) > 1 from a;
>
>> And if that's not efficient, why not optimise _that_, since it
>> expresses directly what you want?
>
>Practicality, mostly. To do it that way, the optimizer would have
>to have extremely specific hard-wired knowledge about the behavior
>of count() (which flies in the face of Postgres' open-ended approach
>to aggregate functions);

Actually, the aggregate interface could pass in a predicate test that
the aggregate function could use to say "stop" once it knows that
the result of the predicate will be true at the end of the query.

Of the standard aggregates, "count()" is probably the only one that
could make use of it. And of course only rarely is count() used
in such a way.

As someone who has long made his living implementing optimizing
compilers, I don't think that optimizing expressions such as the
one Chris mentions is all that difficult a task.

But there are far more important things to think about implementing
in Postgres.

>I have currently got it working (I think; not too well tested yet)
>using the proposal I offered before of "pay attention to the size
>of LIMIT, but ignore OFFSET", so that the same query plan will be
>derived from similar queries with different OFFSETs. Does anyone
>have a substantial gripe with that compromise?

Not me, that's for sure.

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.