On 11/01/23 06:18, David Rowley wrote:
Not sure if we should be trying to improve that in this patch. I just
wanted to identify it as something else that perhaps could be done.
This could be within reach but still original problem of having hashagg
removing
any gains from this remains.
eg
set enable_hashagg=0;
explain select distinct relkind, relname, count(*) over (partition by
relkind) from pg_Class;
QUERY PLAN
------------------------------------------------------------------------------------
Unique (cost=41.26..65.32 rows=412 width=73)
-> Incremental Sort (cost=41.26..62.23 rows=412 width=73)
Sort Key: relkind, relname, (count(*) OVER (?))
Presorted Key: relkind
-> WindowAgg (cost=36.01..43.22 rows=412 width=73)
-> Sort (cost=36.01..37.04 rows=412 width=65)
Sort Key: relkind
-> Seq Scan on pg_class (cost=0.00..18.12 rows=412
width=65)
(8 rows)
reset enable_hashagg;
explain select distinct relkind, relname, count(*) over (partition by
relkind) from pg_Class;
QUERY PLAN
------------------------------------------------------------------------------
HashAggregate (cost=46.31..50.43 rows=412 width=73)
Group Key: relkind, relname, count(*) OVER (?)
-> WindowAgg (cost=36.01..43.22 rows=412 width=73)
-> Sort (cost=36.01..37.04 rows=412 width=65)
Sort Key: relkind
-> Seq Scan on pg_class (cost=0.00..18.12 rows=412 width=65)
(6 rows)
HashAgg has better cost than Unique even with incremental sort (tried
with other case
where we have more columns pushed down but still hashAgg wins).
explain select distinct a, b, count(*) over (partition by a order by b) from
abcd;
QUERY PLAN
--------------------------------------------------------------------------------------
Unique (cost=345712.12..400370.25 rows=1595 width=16)
-> Incremental Sort (cost=345712.12..395456.14 rows=655214 width=16)
Sort Key: a, b, (count(*) OVER (?))
Presorted Key: a, b
-> WindowAgg (cost=345686.08..358790.36 rows=655214 width=16)
-> Sort (cost=345686.08..347324.11 rows=655214 width=8)
Sort Key: a, b
-> Seq Scan on abcd (cost=0.00..273427.14 rows=655214
width=8)
explain select distinct a, b, count(*) over (partition by a order by b) from
abcd;
QUERY PLAN
--------------------------------------------------------------------------------
HashAggregate (cost=363704.46..363720.41 rows=1595 width=16)
Group Key: a, b, count(*) OVER (?)
-> WindowAgg (cost=345686.08..358790.36 rows=655214 width=16)
-> Sort (cost=345686.08..347324.11 rows=655214 width=8)
Sort Key: a, b
-> Seq Scan on abcd (cost=0.00..273427.14 rows=655214 width=8)
(6 rows)
I'm not really all that sure the above query shape makes much sense in
the real world. Would anyone ever want to use DISTINCT on some results
containing WindowFuncs?
This could still have been good to have if there were no negative impact
and some benefit in few cases but as mentioned before, if hashagg removes
any sort (which happened due to push down), all gains will be lost
and we will be probably worse off than before.
--
Regards,
Ankit Kumar Pandey
