Re: [HACKERS] Solution for LIMIT cost estimation

Chris Bitmead <chrisb@nimrod.itg.telstra.com.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); furthermore it would have to examine every
query to see if there is a count() - inequality operator - constant
clause placed in such a way that no other result need be delivered
by the query.  That's a lot of mechanism and overhead to extract the
same information that is provided directly by LIMIT; and it doesn't
eliminate the need for LIMIT, since this is only one application
for LIMIT (not even the primary one IMHO).

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?
        regards, tom lane