Re: Performance of "distinct with limit"

Thanks for your insight Jeff. Interesting read!
K

Sent with [ProtonMail](https://protonmail.com) Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Saturday, August 29, 2020 6:23 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Fri, Aug 28, 2020 at 8:34 AM Klaudie Willis <Klaudie(dot)Willis(at)protonmail(dot)com> wrote:
>
>> No index on n, no. Index might solve it yes, but it seems to me such a trivial optimization even without. Obviously it is not.
>>
>> QUERY PLAN |
>> ----------------------------------------------------------------------------------|
>> Limit (cost=1911272.10..1911272.12 rows=2 width=7) |
>> -> HashAggregate (cost=1911272.10..1911282.45 rows=1035 width=7) |
>> Group Key: cfi |
>> -> Seq Scan on bigtable (cost=0.00..1817446.08 rows=37530408 width=7)|
>
> I think it would be nice if the LIMIT functionality could be pushed down into the HashAgg so it could stop early, I've run into this a few times. But it just isn't implemented. It wouldn't be the hardest feature to ever add to PostgreSQL, but it also wouldn't be trivial. It would require coordinated changes both to the planner and to the executor.
>
> Also, the use of LIMIT without an ORDER BY makes the query non-deterministic, which makes it kind of a second-class citizen. There might be more enthusiasm among experienced developers for implementing this if it weren't for that. (Although there may be related deterministic cases in which a similar limited hash agg could be useful.)
>
> In the meantime, an index on "n" would probably cause it to switch to a Unique plan which reads in index order. This plan does get to stop early.
>
> Cheers,
>
> Jeff