Обсуждение: Redundant Unique plan node for table with a unique index
Hello!
There is a table with a unique index on it and we have a query that searching DISTINCT values on this table on columns of unique index. Example:
create table a (n int);
insert into a (n) select x from generate_series(1, 140000) as g(x);
create unique index on a (n);
explain select distinct n from a;
insert into a (n) select x from generate_series(1, 140000) as g(x);
create unique index on a (n);
explain select distinct n from a;
QUERY PLAN
------------------------------------------------------------------------------------
Unique (cost=0.42..6478.42 rows=140000 width=4)
-> Index Only Scan using a_n_idx on a (cost=0.42..6128.42 rows=140000 width=4)
(2 rows)
------------------------------------------------------------------------------------
Unique (cost=0.42..6478.42 rows=140000 width=4)
-> Index Only Scan using a_n_idx on a (cost=0.42..6128.42 rows=140000 width=4)
(2 rows)
We can see that Unique node is redundant for this case. So I implemented a simple patch that removes Unique node from the plan.
After patch:
explain select distinct n from a;
QUERY PLAN
---------------------------------------------------------
Seq Scan on a (cost=0.00..2020.00 rows=140000 width=4)
(1 row)
---------------------------------------------------------
Seq Scan on a (cost=0.00..2020.00 rows=140000 width=4)
(1 row)
The patch is rather simple and doesn't consider queries with joins. The criteria when Unique node is should be removed is a case when a set of Vars in DISTINCT clause contains unique index columns from the same table.
Another example:
CREATE TABLE a (n int, m int);
CRETE UNIQUE INDEX ON a (n);
SELECT DISTINCT (n,m) FROM a;
CRETE UNIQUE INDEX ON a (n);
SELECT DISTINCT (n,m) FROM a;
The Unique node should be deleted because n is contained in (n,m).
The patch doesn't consider these cases:
1. DISTINCT ON [EXPR]
Because this case can need grouping.
2. Subqueries.
Because this case can need grouping:
CREATE TABLE a (n int);
CREA UNIQUE INDEX ON a (n);
SELECT DISTINCT g FROM (SELECT * FROM a) as g;
Because this case can need grouping.
2. Subqueries.
Because this case can need grouping:
CREATE TABLE a (n int);
CREA UNIQUE INDEX ON a (n);
SELECT DISTINCT g FROM (SELECT * FROM a) as g;
3. Joins, because it demands complication of code.
Example:
SELECT DISTINCT a.n1 JOIN b where a.n1 = b.n1;
where a.n1 and b.n1 should be unique indexes and join qual should be on this index columns.
or
a have a unique index on n1 and b is "unique for a" on join qual.
Example:
SELECT DISTINCT a.n1 JOIN b where a.n1 = b.n1;
where a.n1 and b.n1 should be unique indexes and join qual should be on this index columns.
or
a have a unique index on n1 and b is "unique for a" on join qual.
I am wondering if there are opportunities for further development of this patch, in particular for JOIN cases.
For several levels of JOINs we should understand which set columns is unique for the every joinrel in query. In general terms I identified two cases when joinrel "saves" unique index from table: when tables are joined by unique index columns and when one table has unique index and it is "unique_for" (has one common tuple) another table.
Regards,
Damir Belyalov
Postgres Professional
Вложения
> On 13 Sep 2023, at 15:22, Damir Belyalov <dam.bel07@gmail.com> wrote: > There is a table with a unique index on it and we have a query that searching DISTINCT values on this table on columnsof unique index. > We can see that Unique node is redundant for this case. So I implemented a simple patch that removes Unique node from theplan. Is this query pattern common enough to warrant spending time on in the planner (are there perhaps ORMs that generate such)? Have you measured the overhead of this? -- Daniel Gustafsson
On Thu, 14 Sept 2023 at 02:28, Damir Belyalov <dam.bel07@gmail.com> wrote: > create table a (n int); > insert into a (n) select x from generate_series(1, 140000) as g(x); > create unique index on a (n); > explain select distinct n from a; > QUERY PLAN > ------------------------------------------------------------------------------------ > Unique (cost=0.42..6478.42 rows=140000 width=4) > -> Index Only Scan using a_n_idx on a (cost=0.42..6128.42 rows=140000 width=4) > (2 rows) > > > We can see that Unique node is redundant for this case. So I implemented a simple patch that removes Unique node from theplan. I don't think this is a good way to do this. The method you're using only supports this optimisation when querying a table directly. If there were subqueries, joins, etc then it wouldn't work as there are no unique indexes. You should probably have a look at [1] to see further details of an alternative method without the said limitations. David [1] https://postgr.es/m/flat/CAKU4AWqZvSyxroHkbpiHSCEAY2C41dG7VWs%3Dc188KKznSK_2Zg%40mail.gmail.com
I don't think this is a good way to do this. The method you're using
only supports this optimisation when querying a table directly. If
there were subqueries, joins, etc then it wouldn't work as there are
no unique indexes. You should probably have a look at [1] to see
further details of an alternative method without the said limitations.
David
[1] https://postgr.es/m/flat/CAKU4AWqZvSyxroHkbpiHSCEAY2C41dG7VWs%3Dc188KKznSK_2Zg%40mail.gmail.com
The nullable tracking blocker probably has been removed by varnullingrels
so I will start working on UniqueKey stuff very soon, thank you David
for remember of this feature!
Best Regards
Andy Fan
Thank you for feedback and thread [1].
Regards,
Damir Belyalov
Postgres Professional