gpdb
gpdb copied to clipboard
Published
20 hours ago •
greenplum-db
greenplum-db
Distinct operation on replicated table returns incorrect results
Bug Report
The distinct operation is performed on the replicated table, and the results are not as expected. It can be seen from the execution plan that the internal execution process is wrong.
Greenplum version or build
- GP: 6.19.1
OS version and uname -a
- Linux gp6mdw 5.4.0-90-generic # 101-Ubuntu SMP Fri Oct 15 20:00:55 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux
Expected behavior
Actual behavior
Step to reproduce the behavior
- Prerequisite:
DROP TABLE IF EXISTS AOI_REP; CREATE TEMP TABLE AOI_REP AS
SELECT '100022789468-00' AS WORKORDER
UNION ALL
SELECT '100022789468-00'
DISTRIBUTED REPLICATED;
CREATE TABLE dw.fact_sn_info
(
mcbsno character varying(11),
workorder character varying(20)
)
WITH (
OIDS = FALSE
)
-- TABLESPACE tbs_ssd01
DISTRIBUTED BY (mcbsno)
;
-- insert 300 records
insert into dw.fact_sn_info values ('XD21NP2321','100022789468-00'), ('XD21NP2237','100022789468-00')...;
- Something wrong with
DISTINCT:
F6_BDC=# SELECT S.WORKORDER,COUNT(1) AS QTY
F6_BDC=# FROM DW.FACT_SN_INFO S
F6_BDC=# WHERE WORKORDER in (SELECT DISTINCT WORKORDER FROM AOI_REP)
F6_BDC=# GROUP BY S.WORKORDER;
workorder | qty
-----------------+-----
100022789468-00 | 15
(1 行记录)
F6_BDC=# explain analyse SELECT S.WORKORDER,COUNT(1) AS QTY
F6_BDC=# FROM DW.FACT_SN_INFO S
F6_BDC=# WHERE WORKORDER in (SELECT DISTINCT WORKORDER FROM AOI_REP)
F6_BDC=# GROUP BY S.WORKORDER;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 20:1 (slice3; segments: 20) (cost=0.00..437.65 rows=1 width=23) (actual time=2.181..2.223 rows=1 loops=1)
-> GroupAggregate (cost=0.00..437.65 rows=1 width=23) (actual time=1.890..1.890 rows=1 loops=1)
Group Key: fact_sn_info.workorder
-> Sort (cost=0.00..437.65 rows=1 width=23) (actual time=1.882..1.883 rows=11 loops=1)
Sort Key: fact_sn_info.workorder
Sort Method: quicksort Memory: 660kB
-> Redistribute Motion 20:20 (slice2; segments: 20) (cost=0.00..437.65 rows=1 width=23) (actual time=0.956..1.819 rows=11 loops=1)
Hash Key: fact_sn_info.workorder
-> Result (cost=0.00..437.65 rows=1 width=23) (actual time=0.198..0.201 rows=1 loops=1)
-> HashAggregate (cost=0.00..437.65 rows=1 width=23) (actual time=0.197..0.200 rows=1 loops=1)
Group Key: fact_sn_info.workorder
Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 32 buckets; total 0 expansions.
-> HashAggregate (cost=0.00..437.63 rows=129 width=15) (actual time=0.036..0.042 rows=2 loops=1)
Group Key: fact_sn_info.workorder, fact_sn_info.ctid, fact_sn_info.gp_segment_id
Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 32 buckets; total 0 expansions.
Extra Text: (seg4) Hash chain length 1.0 avg, 1 max, using 2 of 32 buckets; total 0 expansions.
-> Redistribute Motion 1:20 (slice1; segments: 1) (cost=0.00..437.56 rows=2568 width=25) (actual time=0.004..0.005 rows=2 loops=1)
Hash Key: fact_sn_info.ctid, fact_sn_info.gp_segment_id
-> Nested Loop (cost=0.00..437.41 rows=2568 width=25) (actual time=0.073..0.100 rows=15 loops=1)
Join Filter: true
-> GroupAggregate (cost=0.00..431.00 rows=1 width=16) (actual time=0.033..0.034 rows=1 loops=1)
Group Key: aoi_rep.workorder
-> Sort (cost=0.00..431.00 rows=2 width=16) (actual time=0.028..0.031 rows=2 loops=1)
Sort Key: aoi_rep.workorder
Sort Method: quicksort Memory: 33kB
-> Seq Scan on aoi_rep (cost=0.00..431.00 rows=2 width=16) (actual time=0.004..0.004 rows=2 loops=1)
-> Index Scan using idx_dw_fact_sn_info_workorder on fact_sn_info (cost=0.00..6.18 rows=129 width=25) (actual time=0.036..0.058 rows=15 loops=1)
Index Cond: ((workorder)::text = aoi_rep.workorder)
Planning time: 32.920 ms
(slice0) Executor memory: 296K bytes.
(slice1) Executor memory: 132K bytes (seg6). Work_mem: 33K bytes max.
(slice2) Executor memory: 175K bytes avg x 20 workers, 240K bytes max (seg0).
(slice3) Executor memory: 64K bytes avg x 20 workers, 64K bytes max (seg0). Work_mem: 33K bytes max.
(slice4)
Memory used: 204800kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 3.720 ms
(40 行记录)
- Without
DISTINCT:
F6_BDC=# SELECT S.WORKORDER,COUNT(1) AS QTY
F6_BDC=# FROM DW.FACT_SN_INFO S
F6_BDC=# WHERE WORKORDER in (SELECT WORKORDER FROM AOI_REP)
F6_BDC=# GROUP BY S.WORKORDER;
workorder | qty
-----------------+-----
100022789468-00 | 300
(1 行记录)
F6_BDC=# explain analyse SELECT S.WORKORDER,COUNT(1) AS QTY
F6_BDC=# FROM DW.FACT_SN_INFO S
F6_BDC=# WHERE WORKORDER in (SELECT WORKORDER FROM AOI_REP)
F6_BDC=# GROUP BY S.WORKORDER;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 20:1 (slice3; segments: 20) (cost=0.00..443.58 rows=1 width=23) (actual time=2.783..2.790 rows=1 loops=1)
-> GroupAggregate (cost=0.00..443.58 rows=1 width=23) (actual time=2.247..2.247 rows=1 loops=1)
Group Key: fact_sn_info.workorder
-> Sort (cost=0.00..443.58 rows=1 width=23) (actual time=2.237..2.237 rows=20 loops=1)
Sort Key: fact_sn_info.workorder
Sort Method: quicksort Memory: 660kB
-> Redistribute Motion 20:20 (slice2; segments: 20) (cost=0.00..443.58 rows=1 width=23) (actual time=1.767..2.164 rows=20 loops=1)
Hash Key: fact_sn_info.workorder
-> Result (cost=0.00..443.58 rows=1 width=23) (actual time=0.937..0.940 rows=1 loops=1)
-> HashAggregate (cost=0.00..443.58 rows=1 width=23) (actual time=0.937..0.940 rows=1 loops=1)
Group Key: fact_sn_info.workorder
Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 32 buckets; total 0 expansions.
-> HashAggregate (cost=0.00..443.55 rows=257 width=15) (actual time=0.729..0.740 rows=21 loops=1)
Group Key: fact_sn_info.workorder, fact_sn_info.ctid, fact_sn_info.gp_segment_id
Extra Text: (seg5) Hash chain length 1.4 avg, 3 max, using 15 of 32 buckets; total 0 expansions.
-> Redistribute Motion 20:20 (slice1; segments: 20) (cost=0.00..443.42 rows=257 width=25) (actual time=0.055..0.681 rows=42 loops=1)
Hash Key: fact_sn_info.ctid, fact_sn_info.gp_segment_id
-> Nested Loop (cost=0.00..443.40 rows=257 width=25) (actual time=0.042..0.100 rows=36 loops=1)
Join Filter: true
-> Seq Scan on aoi_rep (cost=0.00..431.00 rows=2 width=16) (actual time=0.004..0.005 rows=2 loops=1)
-> Index Scan using idx_dw_fact_sn_info_workorder on fact_sn_info (cost=0.00..12.36 rows=129 width=25) (actual time=0.017..0.043 rows=18 loops=2)
Index Cond: ((workorder)::text = aoi_rep.workorder)
Planning time: 20.548 ms
(slice0) Executor memory: 296K bytes.
(slice1) Executor memory: 100K bytes avg x 20 workers, 100K bytes max (seg0).
(slice2) Executor memory: 240K bytes avg x 20 workers, 240K bytes max (seg0).
(slice3) Executor memory: 64K bytes avg x 20 workers, 64K bytes max (seg0). Work_mem: 33K bytes max.
(slice4)
Memory used: 204800kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 4.613 ms
(33 行记录)
In my local env, planner's plan seems OK (semi join):
-- with distinct
QUERY PLAN
--------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice2; segments: 2) (cost=3.17..3.18 rows=1 width=66)
-> HashAggregate (cost=3.17..3.18 rows=1 width=66)
Group Key: s.workorder
-> Redistribute Motion 2:2 (slice1; segments: 2) (cost=3.13..3.15 rows=1 width=66)
Hash Key: s.workorder
-> HashAggregate (cost=3.13..3.13 rows=1 width=66)
Group Key: s.workorder
-> Hash Semi Join (cost=1.06..3.12 rows=2 width=16)
Hash Cond: ((s.workorder)::text = aoi_rep.workorder)
-> Seq Scan on fact_sn_info s (cost=0.00..2.02 rows=1 width=16)
-> Hash (cost=1.04..1.04 rows=1 width=16)
-> HashAggregate (cost=1.02..1.03 rows=1 width=32)
Group Key: aoi_rep.workorder
-> Seq Scan on aoi_rep (cost=0.00..1.02 rows=2 width=16)
Optimizer: Postgres query optimizer
(15 rows)
-- with out
QUERY PLAN
-----------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice2; segments: 2) (cost=3.16..3.17 rows=1 width=66)
-> HashAggregate (cost=3.16..3.17 rows=1 width=66)
Group Key: s.workorder
-> Redistribute Motion 2:2 (slice1; segments: 2) (cost=3.12..3.14 rows=1 width=66)
Hash Key: s.workorder
-> HashAggregate (cost=3.12..3.12 rows=1 width=66)
Group Key: s.workorder
-> Hash Semi Join (cost=1.04..3.11 rows=2 width=16)
Hash Cond: ((s.workorder)::text = aoi_rep.workorder)
-> Seq Scan on fact_sn_info s (cost=0.00..2.02 rows=1 width=16)
-> Hash (cost=1.02..1.02 rows=1 width=16)
-> Seq Scan on aoi_rep (cost=0.00..1.02 rows=2 width=16)
Optimizer: Postgres query optimizer
(13 rows)
@cobolbaby Can you please re-run the above test with optimizer = off in your env? And paste the result here.
Thanks!
F6_BDC=# set optimizer = off;
SET
F6_BDC=# SELECT S.WORKORDER,COUNT(1) AS QTY FROM DW.FACT_SN_INFO S WHERE WORKORDER in (SELECT DISTINCT WORKORDER FROM AOI_REP) GROUP BY S.WORKORDER;
workorder | qty
-----------------+-----
100022789468-00 | 300
(1 行记录)
F6_BDC=# explain analyse SELECT S.WORKORDER,COUNT(1) AS QTY FROM DW.FACT_SN_INFO S WHERE WORKORDER in (SELECT DISTINCT WORKORDER FROM AOI_REP) GROUP BY S.WORKORDER;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 20:1 (slice2; segments: 20) (cost=247125.18..247150.76 rows=2558 width=66) (actual time=2.504..2.832 rows=1 loops=1)
-> HashAggregate (cost=247125.18..247150.76 rows=128 width=66) (actual time=2.517..2.524 rows=1 loops=1)
Group Key: s.workorder
Extra Text: (seg2) Hash chain length 1.0 avg, 1 max, using 1 of 512 buckets; total 0 expansions.
-> Redistribute Motion 20:20 (slice1; segments: 20) (cost=247035.65..247086.81 rows=128 width=66) (actual time=1.538..2.479 rows=20 loops=1)
Hash Key: s.workorder
-> HashAggregate (cost=247035.65..247035.65 rows=128 width=66) (actual time=0.399..0.402 rows=1 loops=1)
Group Key: s.workorder
Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 512 buckets; total 0 expansions.
-> Nested Loop (cost=421.04..247022.86 rows=128 width=15) (actual time=0.324..0.372 rows=18 loops=1)
-> HashAggregate (cost=1.02..1.03 rows=1 width=32) (actual time=0.015..0.022 rows=1 loops=1)
Group Key: aoi_rep.workorder
Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 32 buckets; total 0 expansions.
-> Seq Scan on aoi_rep (cost=0.00..1.02 rows=2 width=16) (actual time=0.002..0.004 rows=2 loops=1)
-> Bitmap Heap Scan on fact_sn_info s (cost=420.02..246996.23 rows=128 width=15) (actual time=0.306..0.343 rows=18 loops=1)
Recheck Cond: ((workorder)::text = aoi_rep.workorder)
-> Bitmap Index Scan on idx_dw_fact_sn_info_workorder (cost=0.00..419.38 rows=128 width=0) (actual time=0.240..0.240 rows=18 loops=1)
Index Cond: ((workorder)::text = aoi_rep.workorder)
Planning time: 0.528 ms
(slice0) Executor memory: 288K bytes.
(slice1) Executor memory: 1134K bytes avg x 20 workers, 1134K bytes max (seg0). Work_mem: 97K bytes max.
(slice2) Executor memory: 67K bytes avg x 20 workers, 136K bytes max (seg2).
Memory used: 204800kB
Optimizer: Postgres query optimizer
Execution time: 4.881 ms
(28 行记录)
F6_BDC=# SELECT S.WORKORDER,COUNT(1) AS QTY FROM DW.FACT_SN_INFO S WHERE WORKORDER in (SELECT WORKORDER FROM AOI_REP) GROUP BY S.WORKORDER;
workorder | qty
-----------------+-----
100022789468-00 | 300
(1 行记录)
F6_BDC=# explain analyse SELECT S.WORKORDER,COUNT(1) AS QTY FROM DW.FACT_SN_INFO S WHERE WORKORDER in (SELECT WORKORDER FROM AOI_REP) GROUP BY S.WORKORDER;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 20:1 (slice2; segments: 20) (cost=246935.91..246961.49 rows=2558 width=66) (actual time=1.990..2.364 rows=1 loops=1)
-> HashAggregate (cost=246935.91..246961.49 rows=128 width=66) (actual time=2.099..2.109 rows=1 loops=1)
Group Key: s.workorder
Extra Text: (seg2) Hash chain length 1.0 avg, 1 max, using 1 of 512 buckets; total 0 expansions.
-> Redistribute Motion 20:20 (slice1; segments: 20) (cost=246846.38..246897.54 rows=128 width=66) (actual time=1.156..2.051 rows=20 loops=1)
Hash Key: s.workorder
-> HashAggregate (cost=246846.38..246846.38 rows=128 width=66) (actual time=0.254..0.258 rows=1 loops=1)
Group Key: s.workorder
Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 512 buckets; total 0 expansions.
-> Nested Loop (cost=421.04..246833.59 rows=128 width=15) (actual time=0.154..0.228 rows=18 loops=1)
-> HashAggregate (cost=1.02..1.03 rows=1 width=32) (actual time=0.016..0.023 rows=1 loops=1)
Group Key: aoi_rep.workorder
Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 32 buckets; total 0 expansions.
-> Seq Scan on aoi_rep (cost=0.00..1.02 rows=2 width=16) (actual time=0.003..0.003 rows=2 loops=1)
-> Bitmap Heap Scan on fact_sn_info s (cost=420.02..246806.97 rows=128 width=15) (actual time=0.135..0.187 rows=18 loops=1)
Recheck Cond: ((workorder)::text = aoi_rep.workorder)
-> Bitmap Index Scan on idx_dw_fact_sn_info_workorder (cost=0.00..419.38 rows=128 width=0) (actual time=0.072..0.072 rows=18 loops=1)
Index Cond: ((workorder)::text = aoi_rep.workorder)
Planning time: 0.623 ms
(slice0) Executor memory: 288K bytes.
(slice1) Executor memory: 1134K bytes avg x 20 workers, 1134K bytes max (seg0). Work_mem: 97K bytes max.
(slice2) Executor memory: 67K bytes avg x 20 workers, 136K bytes max (seg2).
Memory used: 204800kB
Optimizer: Postgres query optimizer
Execution time: 4.292 ms
(28 行记录)
@kainwen The problem seems to be caused by gporca.
@cobolbaby are you able to attach a minidump for this query? I haven't been able to reproduce your plan so this could help.
Steps to collect minidump:
- Set guc:
SET optimizer_minidump=always;
- Run query:
SELECT S.WORKORDER,COUNT(1) AS QTY
FROM DW.FACT_SN_INFO S
WHERE WORKORDER in (SELECT DISTINCT WORKORDER FROM AOI_REP)
GROUP BY S.WORKORDER;
- Collect file
ls $MASTER_DATA_DIRECTORY/minidumps/Minidump_*.mdp
