Incorrect locus type causes wrong plan

29 views
Skip to first unread message

Hao Wu

unread,
Jun 30, 2020, 11:18:54 AM6/30/20
to Greenplum Developers, Zhenghua Lyu
Hi hackers,

Recently, when I handle the Github issue(https://github.com/greenplum-db/gpdb/issues/10226), Zhenghua and I found that there are many cases like the problem causing wrong plans.
Before analysis, let's see some wrong plans below. FYI, `rpt` is a replicated table, prt is a hash distributed table.

case 1:
```
 gpadmin=# explain insert into rpt select random() * 200 from generate_series(1,100);
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Insert on rpt  (cost=0.00..27.50 rows=334 width=4)
   ->  Subquery Scan on "*SELECT*"  (cost=0.00..27.50 rows=334 width=4)
         ->  Function Scan on generate_series  (cost=0.00..15.00 rows=334 width=8)
 Optimizer: Postgres query optimizer
```

case 2:
```
gpadmin=# explain update rpt set i = random()*100;
                           QUERY PLAN
-----------------------------------------------------------------
 Update on rpt  (cost=0.00..1785.25 rows=32100 width=14)
   ->  Seq Scan on rpt  (cost=0.00..1785.25 rows=32100 width=14)
 Optimizer: Postgres query optimizer
```

case 3:
```
gpadmin=# explain delete from rpt where i = random()*100;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Delete on rpt  (cost=0.00..2026.00 rows=33 width=10)
   ->  Seq Scan on rpt  (cost=0.00..2026.00 rows=33 width=10)
         Filter: ((i)::double precision = (random() * '100'::double precision))
 Optimizer: Postgres query optimizer
(4 rows)
```

case 4:
```
gpadmin=# explain insert into rpt select i from rpt limit 2;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Insert on rpt  (cost=0.00..431.03 rows=1 width=4)
   ->  Result  (cost=0.00..431.00 rows=1 width=8)
         ->  Limit  (cost=0.00..431.00 rows=1 width=4)
               ->  Seq Scan on rpt rpt_1  (cost=0.00..431.00 rows=1 width=4)
 Optimizer: Pivotal Optimizer (GPORCA)
(5 rows)

gpadmin=# set optimizer=off;
SET
gpadmin=# explain insert into rpt select i from rpt limit 2;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Insert on rpt  (cost=0.00..0.04 rows=1 width=4)
   ->  Limit  (cost=0.00..0.02 rows=1 width=4)
         ->  Seq Scan on rpt rpt_1  (cost=0.00..1063.00 rows=32100 width=4)
 Optimizer: Postgres query optimizer
(4 rows)
```

case 5:
```
gpadmin=# explain select * from prt, (select (random()*100)::int x from rpt)r2 where prt.i=r2.x;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=2266.75..1442677.70 rows=9273690 width=8)
   ->  Hash Join  (cost=2266.75..1257203.90 rows=3091230 width=8)
         Hash Cond: ((((random() * '100'::double precision))::integer) = prt.i)
         ->  Seq Scan on rpt  (cost=0.00..1785.25 rows=32100 width=4)
         ->  Hash  (cost=1063.00..1063.00 rows=32100 width=4)
               ->  Seq Scan on prt  (cost=0.00..1063.00 rows=32100 width=4)
 Optimizer: Postgres query optimizer
(7 rows)
gpadmin=# set optimizer=on;explain select * from prt, (select (random()*100)::int x from rpt)r2 where prt.i=r2.x;
SET
                                  QUERY PLAN
------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=8)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=8)
         Hash Cond: (prt.i = (int4((random() * '100'::double precision))))
         ->  Seq Scan on prt  (cost=0.00..431.00 rows=1 width=4)
         ->  Hash  (cost=431.00..431.00 rows=1 width=4)
               ->  Seq Scan on rpt  (cost=0.00..431.00 rows=1 width=1)
 Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)

gpadmin=# explain select * from prt, (select (random()*100)::int x from generate_series(1,100))r2 where prt.i=r2.x;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..431.06 rows=1 width=8)
   ->  Hash Join  (cost=0.00..431.06 rows=1 width=8)
         Hash Cond: ((int4((random() * '100'::double precision))) = prt.i)
         ->  Result  (cost=0.00..0.00 rows=334 width=1)
               One-Time Filter: (gp_execution_segment() = 2)
               ->  Function Scan on generate_series  (cost=0.00..0.00 rows=334 width=1)
         ->  Hash  (cost=431.00..431.00 rows=1 width=4)
               ->  Broadcast Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=4)
                     ->  Seq Scan on prt  (cost=0.00..431.00 rows=1 width=4)
 Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)

gpadmin=# set optimizer=off;
SET
gpadmin=# explain select * from prt, (select (random()*100)::int x from generate_series(1,100))r2 where prt.i=r2.x;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=40.00..16270.25 rows=96300 width=8)
   ->  Hash Join  (cost=40.00..14344.25 rows=32100 width=8)
         Hash Cond: (prt.i = (((random() * '100'::double precision))::integer))
         ->  Seq Scan on prt  (cost=0.00..1063.00 rows=32100 width=4)
         ->  Hash  (cost=27.50..27.50 rows=334 width=4)
               ->  Function Scan on generate_series  (cost=0.00..17.50 rows=334 width=4)
 Optimizer: Postgres query optimizer
```

case 6:
```
gpadmin=# explain  select * from prt, (select i x from rpt limit 2)r2 where prt.i=r2.x;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.07..1309.84 rows=193 width=8)
   ->  Hash Join  (cost=0.07..1305.98 rows=65 width=8)
         Hash Cond: (prt.i = rpt.i)
         ->  Seq Scan on prt  (cost=0.00..1063.00 rows=32100 width=4)
         ->  Hash  (cost=0.04..0.04 rows=1 width=4)
               ->  Limit  (cost=0.00..0.02 rows=1 width=4)
                     ->  Seq Scan on rpt  (cost=0.00..1063.00 rows=32100 width=4)
 Optimizer: Postgres query optimizer
(8 rows)
```

The root cause is that the mutable functions or LIMIT breaks the declaration of the locus type being General/SegmentGeneral. General/SegmentGeneral means the dataset is self-contained, no matter the order. But mutable functions may derive different dataset on different segments or LIMIT may choose the different subset of the dataset. Both of them leads the produced tuple set to be different on different segment, while their locus type is still treated as General/SegmentGeneral. So, if the subquery is dispatched to the segments to execute, they could produce different content for the replicated table.

To fix the issue, there are some places calling `contain_volatile_functions()`/`contain_mutable_functions()` to amend the locus problem, for example, `create_motion_path_for_insert()`. The bug in `create_motion_path_for_insert()` is that checking the volatile function is not recursive for the subpath.

Currently, we have 3 solutions:
  1. Add a new locus type, like CdbLocusType_SingleSegment, meaning the data is self-contained, but can only run on one segment(excluding the master). Amend the locus type of the path to CdbLocusType_SingleSegment/CdbLocusType_SingleQE if needed before creating the plan. However, adding a new locus type brings much complexity for handling the locus. 
  2. Add checking code when it may add a motion, like the function adjust_modifytable_subpaths(). It's ugly in this way, and maybe it will increase the time in creating the plan. What's more, it misleads us to believe that the locus type of a path containing mutable functions could be still General/SegmentGeneral.
  3. To erase the misleading in solution 2, we could pick up some points to check the subpaths and change the locus type of the subpath containing mutable functions.
Any ideas?

Thank you,
Hao Wu & Zhenghua Lyu

Hao Wu

unread,
Jun 30, 2020, 9:49:58 PM6/30/20
to Ning Yu, Greenplum Developers, Zhenghua Lyu

Currently, we have 3 solutions:
  1. Add a new locus type, like CdbLocusType_SingleSegment, meaning the data is self-contained, but can only run on one segment(excluding the master). Amend the locus type of the path to CdbLocusType_SingleSegment/CdbLocusType_SingleQE if needed before creating the plan. However, adding a new locus type brings much complexity for handling the locus. 
I vote for this option.  If the concern is on adding a new locus type, is it possible for us to reuse the SingleQE with a hint?  For example, in the struct CdbPathLocus we record the locus type, distkey and numsegments, we could add a new one "hints", it could be or'ed with some bits like QD_OK, QE_OK, so we know whether QD/QE is allowed for a SingleQE or Entry.
Sorry, an important behavior I missed to mention is that the locus type of some subpaths is set before the target list(which may contain some mutable functions) is set. 
For example, in `explain insert into rpt select random()*120 from generate_series(1,10);`,
in create_functionscan_path(), the `pathnode->pathtarget->exprs` is NULL, and the locus type of the path is set to CdbLocusType_General. While in function create_subqueryscan_path(), the value `contain_mutable_functions(subpath->pathtarget->exprs)` is true. Between calling create_functionscan_path() and create_subqueryscan_path(), apply_projection_to_path() is called to set the `exprs` in the pathtarget of the functionscan path.

Are we allowed to change the locus type of the path node? What condition?

Ning Yu

unread,
Jun 30, 2020, 10:19:13 PM6/30/20
to Hao Wu, Greenplum Developers, Zhenghua Lyu
(resend, the previous one was not sent to the mailing list successfully)
I vote for this option.  If the concern is on adding a new locus type, is it possible for us to reuse the SingleQE with a hint?  For example, in the struct CdbPathLocus we record the locus type, distkey and numsegments, we could add a new one "hints", it could be or'ed with some bits like QD_OK, QE_OK, so we know whether QD/QE is allowed for a SingleQE or Entry.

Reply all
Reply to author
Forward
0 new messages