Re: Optimizing DISTINCT with LIMIT

tmp <skrald(at)amossen(dot)dk> writes:

> Regarding whether it's worth the effort: In each of my three past jobs (all
> using postgresql) I have met several queries that would fetch a small subset of
> a large - even huge - input. I think that types of queries are relatively
> common out there, but if they are executed for e.g. a web-client it is simply a
> no-go with the current late LIMIT evaluation.
>
> Also, it is my impression that many people use LIMIT to minimize the evaluation
> time of sub queries from which the outer query only needs a small subset of the
> sub query output.

I've seen lots of queries which only pull a subset of the results too -- but
it's always a specific subset. So that means using ORDER BY or a WHERE clause
to control it.

In this example the subset returned is completely arbitrary. That's a much
finer slice of queries.

I would tend to think it's worth it myself. I can see cases where the subset
selected doesn't really matter -- for instance if you're only testing whether
there are at least a certain number of distinct values. Or if you're using up
some inventory and it's not important what order you use them up only that you
fetch some candidate inventory and process them.

But I can also see Tom's reluctance. It's a fair increase in the amount of
code to maintain in that file for a pretty narrow use case. On the other hand
it looks like it would be all in that file. The planner wouldn't have to do
anything special to set it up which is nice.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!