On 5 November 2017 at 04:20, 刘瑞 <whx20...@gmail.com> wrote:
> CREATE TABLE test_tbl ( k INT PRIMARY KEY, col text)
> INSERT into test_tbl select generate_series(1,10000000), 'test';
> test=# explain analyze select distinct col, k from test_tbl order by k limit
> 1000;
>                                                                 QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=1277683.22..1277690.72 rows=1000 width=36) (actual
> time=12697.994..12698.382 rows=1000 loops=1)
>    ->  Unique  (cost=1277683.22..1329170.61 rows=6864985 width=36) (actual
> time=12697.992..12698.311 rows=1000 loops=1)
>          ->  Sort  (cost=1277683.22..1294845.68 rows=6864985 width=36)
> (actual time=12697.991..12698.107 rows=1000 loops=1)
>                Sort Key: k, col
>                Sort Method: external sort  Disk: 215064kB
>                ->  Seq Scan on test_tbl  (cost=0.00..122704.85 rows=6864985
> width=36) (actual time=0.809..7561.215 rows=10000000 loops=1)
>  Planning time: 2.368 ms
>  Execution time: 12728.471 ms
> (8 rows)

The current planner does not make much of an effort into recording
which columns remain distinct at each level. I have ideas on how to
improve this and it would include improving your case here.

9.6 did improve a slight variation of your query, but this was for
GROUP BY instead of DISTINCT. Probably there's no reason why the same
optimisation could not be applied to DISTINCT, I just didn't think of
it when writing the patch.

The item from the release notes [1] reads "Ignore GROUP BY columns
that are functionally dependent on other columns"

So, if you were to write the query as:

explain analyze select col, k from test_tbl group by col, k order by k
limit 1000;

It should run much more quickly, although still not as optimal as it could be.

[1] https://www.postgresql.org/docs/9.6/static/release-9-6.html

 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:

Reply via email to