Reordering DISTINCT keys to match input path's pathkeys

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Reordering DISTINCT keys to match input path's pathkeys
Date: 2024-01-23 05:55:54
Message-ID: CAMbWs48dR26cCcX0f=8bja2JKQPcU64136kHk=xekHT9xschiQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Similar to what we did to GROUP BY keys in 0452b461bc, I think we can do
the same to DISTINCT keys, i.e. reordering DISTINCT keys to match input
path's pathkeys, which can help reduce cost by avoiding unnecessary
re-sort, or allowing us to use incremental-sort to save efforts. For
instance,

create table t (a int, b int);
create index on t (a, b);

explain (costs off) select distinct b, a from t limit 10;
QUERY PLAN
--------------------------------------------------
Limit
-> Unique
-> Index Only Scan using t_a_b_idx on t
(3 rows)

Please note that the parser has ensured that the DISTINCT pathkeys
matches the order of ORDER BY clauses. So there is no need to do this
part again.

In principle, we can perform such reordering for DISTINCT ON too, but we
need to make sure that the resulting pathkeys matches initial ORDER BY
keys, which seems not trivial. So it doesn't seem worth the effort.

Attached is a patch for this optimization. Any thoughts?

Thanks
Richard

Attachment Content-Type Size
v1-0001-Reordering-DISTINCT-keys-to-match-input-path-s-pathkeys.patch application/octet-stream 27.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2024-01-23 06:07:10 Re: Network failure may prevent promotion
Previous Message Michael Paquier 2024-01-23 05:50:06 Re: Test slots invalidations in 035_standby_logical_decoding.pl only if dead rows are removed