cloudberry: [Bug] Optimizer could choose HashAggregate to dedup based on cost.

Cloudberry Database version

main

What happened

Optimizer could produce better plan to dedup based on cost.

What you think should happen instead

No response

How to reproduce

For the planner, we need to create better plan which uses HashAggregate to dedup columns. For example,

ebi_dwh=# explain analyze WITH UniqueIDs AS (
    SELECT DISTINCT gsp_id
    FROM eft_com_call_statistic
)
SELECT COUNT(*)
FROM UniqueIDs;
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=0.00..1419.47 rows=1 width=8) (actual time=2510.079..2510.081 rows=1 loops=1)
   ->  Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..1419.47 rows=1 width=8) (actual time=1998.786..2510.054 rows=4 loops=1)
         ->  Partial Aggregate  (cost=0.00..1419.47 rows=1 width=8) (actual time=1993.802..1993.803 rows=1 loops=1)
               ->  HashAggregate  (cost=0.00..1419.47 rows=2315005 width=1) (actual time=1294.980..1855.013 rows=2339996 loops=1)
                     Group Key: gsp_id
                     ->  Seq Scan on eft_com_call_statistic  (cost=0.00..969.33 rows=2375680 width=37) (actual time=0.343..339.551 rows=2377251 loops=1)
 Planning Time: 5.357 ms
   (slice0)    Executor memory: 98354K bytes.
   (slice1)    Executor memory: 244665K bytes avg x 4x(0) workers, 244755K bytes max (seg2).  Work_mem: 286737K bytes max.
 Memory used:  2097152kB
 Optimizer: Pivotal Optimizer (GPORCA)
 Execution Time: 2584.618 ms
(12 rows)

This plan uses HashAggregate to perform gsp_id deduplication, while

 ebi_dwh=# explain analyze select count(distinct gsp_id) from eft_com_call_statistic;
                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=0.00..1142.67 rows=1 width=8) (actual time=10573.416..10573.418 rows=1 loops=1)
   ->  Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..1142.67 rows=1 width=8) (actual time=10205.645..10573.387 rows=4 loops=1)
         ->  Partial Aggregate  (cost=0.00..1142.67 rows=1 width=8) (actual time=10437.005..10437.007 rows=1 loops=1)
               ->  Seq Scan on eft_com_call_statistic  (cost=0.00..969.33 rows=2375680 width=37) (actual time=0.269..548.673 rows=2377251 loops=1)
 Planning Time: 4.608 ms
   (slice0)    Executor memory: 37K bytes.
   (slice1)    Executor memory: 148480K bytes avg x 4x(0) workers, 148579K bytes max (seg2).
 Memory used:  2097152kB
 Optimizer: Pivotal Optimizer (GPORCA)
 Execution Time: 10573.853 ms
(10 rows)

It seems the cost estimation is no accurate.

Operating System

No specific

Anything else

No

Are you willing to submit PR?

  • Yes, I am willing to submit a PR!

Code of Conduct

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 24 (18 by maintainers)

Most upvoted comments

image

that will speed the count distinct but for a lot of other queries we would need to have optimizer set to on

Yeah. We also need to enable it for ORCA.

For SQL: select count(distinct id) from ao; I found we already have the codes to add an additional DEDUP HashAgg path in PG planner. Yes, there was a path like that, HashAgg on Seqscan to DEDUP, but it’s dropped due to cost compared to a normal two-stage aggregation path by planner.

Correction: after some debug, I found there is no such a path, the DEDUP HashAgg currently we have is much like a one-phase agg:

explain(analyze)  select count(distinct id) from ao ;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
-----------------
 Aggregate  (cost=20917.17..20917.18 rows=1 width=8) (actual time=1237.827..1237.830 rows=1 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=20898.00..20914.67 rows=1000 width=4) (actual time=1131.395..1237.586 ro
ws=1000 loops=1)
         ->  HashAggregate  (cost=20898.00..20901.33 rows=333 width=4) (actual time=1212.114..1212.211 rows=340 loops=1)
               Group Key: id
               ->  Seq Scan on ao  (cost=0.00..14071.33 rows=1365333 width=4) (actual time=1.418..646.267 rows=1392640 loops=1)
 Planning Time: 0.761 ms
   (slice0)    Executor memory: 114K bytes.
   (slice1)    Executor memory: 286K bytes avg x 3x(0) workers, 286K bytes max (seg2).  Work_mem: 61K bytes max.
 Memory used:  128000kB
 Optimizer: Postgres query optimizer
 Execution Time: 1238.826 ms
(11 rows)

That’s not we expected, it gather all live tuples to do agg on master. We need to generate a plan to handle DISTINCT as the CTE plan does and let it win according to user’s cases. That is much like 3-phase AGG in GPDB, and I’m working on it.

Please check the description of this issue above. You can create a very simple table, and insert some data. Then, you can use the above SQL statements to reproduce this issue.