Re: [HACKERS] Solution for LIMIT cost estimation

Don Baccus wrote:

> >> 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.

That's the kind of thing I had in mind.

> 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.

I think a lot of the agregates could make use of it. For example, tell
me all the departments who have spent more than $1000,000 this year...

select deptid, sum(amount) > 1000000 from purchases group by deptid;

>
> 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.

Yep.

>
> >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.