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)| Klaudie ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Friday, August 28, 2020 1:59 PM, <luis.robe...@siscobra.com.br> wrote: > Hi, > > If "n" is indexed, it should run quickly. Can you share the execution plan > for your query? > > --------------------------------------------------------------- > > De: "Klaudie Willis" <klaudie.wil...@protonmail.com> > Para: "pgsql-general" <pgsql-general@lists.postgresql.org> > Enviadas: Sexta-feira, 28 de agosto de 2020 8:29:58 > Assunto: Performance of "distinct with limit" > > Hi, > > Ran into this under-optimized query execution. > > select distinct n from bigtable; -- Lets say this takes 2 minutes > select distinct n from bigtable limit 2 -- This takes approximately the same > time > > However, the latter should have the potential to be so much quicker. I > checked the same query on MSSQL (with 'top 2'), and it seems to do exactly > the optimization I would expect. > > Is there any way to achieve a similar speedup in Postgresql? > > Klaudie
