Search Postgresql Archives

Re: Performance of "distinct with limit"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Aug 28, 2020 at 8:34 AM Klaudie Willis <Klaudie.Willis@xxxxxxxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux