On Thu, Apr 28, 2022 at 9:53 AM Robert Haas <robertmh...@gmail.com> wrote:

> On Fri, Apr 22, 2022 at 11:55 AM David G. Johnston
> <david.g.johns...@gmail.com> wrote:
> > On Wed, Apr 20, 2022 at 11:38 PM bu...@sohu.com <bu...@sohu.com> wrote:
> >>
> >> > > for now fuction cost_subqueryscan always using *total* rows even
> parallel
> >> > > path. like this:
> >> > >
> >> > > Gather (rows=30000)
> >> > >   Workers Planned: 2
> >> > >   ->  Subquery Scan  (rows=30000) -- *total* rows, should be equal
> subpath
> >> > >         ->  Parallel Seq Scan  (rows=10000)
> >> >
> >> > OK, that's bad.
> >
>
> Gather doesn't require a parallel aware subpath, just a parallel-safe
> subpath. In a case like this, the parallel seq scan will divide the
> rows from the underlying relation across the three processes executing
> it. Each process will pass the rows it receives through its own copy
> of the subquery scan. Then, the Gather node will collect all the rows
> from all the workers to produce the final result.
>
>
Thank you.  I think I got off on a tangent there and do understand the
general design here better now.

I feel like the fact that the 2.4 divisor (for 2 planned workers) isn't
shown in the explain plan anywhere is an oversight.

To move the original complaint forward a bit I am posting the three plan
changes that using path->subpath->rows provokes in the regression tests.

======================================================================
 --
 -- Check for incorrect optimization when IN subquery contains a SRF
 --
 select * from int4_tbl o where (f1, f1) in
   (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1);

The material difference between the existing plan and this one is the
estimation of 250 rows
here compared to 1 row.
So (rel.rows != path->subpath->rows) at the top of cost_subqueryscan
+               ->  Subquery Scan on "ANY_subquery"  (cost=1.06..9.28
rows=250 width=8)
+                     Output: "ANY_subquery".f1, "ANY_subquery".g
+                     Filter: ("ANY_subquery".f1 = "ANY_subquery".g)
+                     ->  Result  (cost=1.06..6.15 rows=250 width=8)
======================================================================
The second plan change is basically this same thing, going from rows=4 to
rows=1
causes the plan to include a materialize node.  The shape for purposes of
the security barrier
remains correct.
======================================================================
select * from t union select * from t order by 1,3;
Gather here costs 2,600 vs the Append being 2,950 in the existing plan
shape.
+               ->  Gather  (cost=0.00..2600.00 rows=120000 width=12)
+                     Workers Planned: 2
+                     ->  Parallel Append  (cost=0.00..2600.00 rows=50000
width=12)
+                           ->  Parallel Seq Scan on t  (cost=0.00..575.00
rows=25000 width=12)
+                           ->  Parallel Seq Scan on t t_1
 (cost=0.00..575.00 rows=25000 width=12)
=======================================================================

I've attached the two raw regression output diffs.

Using path->subpath->rows ignores the impact of the node's own filters, but
the base pre-filter number is/should be the correct one; though it is
difficult to say that with certainty when most of these nodes are discarded
and one cannot debug in the middle but only observe the end results.
Disabling that optimization is presently beyond my skill though I may take
it up anyway as its likely still orders easier to do, and then hope some of
these plans produce using data to check with, than actually diving into a C
debugger for the first time.

Reverse engineering the 350 difference may be another approach - namely is
it strictly due to the different plan shape or is it due to the number of
rows.  The fact that the row difference is 35,000 and the cost is 1%
(cpu_tuple_cost = 0.01) of that number seems like a red herring after
thinking it through...to many scans plus the differing shapes.

David J.
diff -U3 /home/vagrant/postgresql/src/test/regress/expected/subselect.out 
/home/vagrant/postgresql/src/test/regress/results/subselect.out
--- /home/vagrant/postgresql/src/test/regress/expected/subselect.out    
2022-04-11 02:01:56.846066150 +0000
+++ /home/vagrant/postgresql/src/test/regress/results/subselect.out     
2022-04-29 00:12:41.656445011 +0000
@@ -1298,29 +1298,29 @@
 --
 -- Check for incorrect optimization when IN subquery contains a SRF
 --
-explain (verbose, costs off)
+explain (verbose)
 select * from int4_tbl o where (f1, f1) in
   (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1);
-                            QUERY PLAN                             
--------------------------------------------------------------------
- Nested Loop Semi Join
+                                             QUERY PLAN                        
                      
+-----------------------------------------------------------------------------------------------------
+ Nested Loop Semi Join  (cost=1.06..10.40 rows=1 width=4)
    Output: o.f1
    Join Filter: (o.f1 = "ANY_subquery".f1)
-   ->  Seq Scan on public.int4_tbl o
+   ->  Seq Scan on public.int4_tbl o  (cost=0.00..1.05 rows=5 width=4)
          Output: o.f1
-   ->  Materialize
+   ->  Materialize  (cost=1.06..9.28 rows=1 width=8)
          Output: "ANY_subquery".f1, "ANY_subquery".g
-         ->  Subquery Scan on "ANY_subquery"
+         ->  Subquery Scan on "ANY_subquery"  (cost=1.06..9.28 rows=1 width=8)
                Output: "ANY_subquery".f1, "ANY_subquery".g
                Filter: ("ANY_subquery".f1 = "ANY_subquery".g)
-               ->  Result
+               ->  Result  (cost=1.06..6.15 rows=250 width=8)
                      Output: i.f1, ((generate_series(1, 50)) / 10)
-                     ->  ProjectSet
+                     ->  ProjectSet  (cost=1.06..2.40 rows=250 width=8)
                            Output: generate_series(1, 50), i.f1
-                           ->  HashAggregate
+                           ->  HashAggregate  (cost=1.06..1.11 rows=5 width=4)
                                  Output: i.f1
                                  Group Key: i.f1
-                                 ->  Seq Scan on public.int4_tbl i
+                                 ->  Seq Scan on public.int4_tbl i  
(cost=0.00..1.05 rows=5 width=4)
                                        Output: i.f1
 (19 rows)
 
diff -U3 /home/vagrant/postgresql/src/test/regress/expected/privileges.out 
/home/vagrant/postgresql/src/test/regress/results/privileges.out
--- /home/vagrant/postgresql/src/test/regress/expected/privileges.out   
2022-04-15 22:58:14.784668286 +0000
+++ /home/vagrant/postgresql/src/test/regress/results/privileges.out    
2022-04-29 00:12:49.520444688 +0000
@@ -363,17 +363,17 @@
 (6 rows)
 
 -- But a security barrier view isolates the leaky operator.
-EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y
+EXPLAIN SELECT * FROM atest12sbv x, atest12sbv y
   WHERE x.a = y.b and abs(y.a) <<< 5;
-             QUERY PLAN              
--------------------------------------
- Nested Loop
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
+ Nested Loop  (cost=0.00..340.15 rows=1 width=16)
    Join Filter: (atest12_1.a = y.b)
-   ->  Subquery Scan on y
+   ->  Subquery Scan on y  (cost=0.00..170.06 rows=1 width=8)
          Filter: (abs(y.a) <<< 5)
-         ->  Seq Scan on atest12
+         ->  Seq Scan on atest12  (cost=0.00..170.00 rows=4 width=8)
                Filter: (b <<< 5)
-   ->  Seq Scan on atest12 atest12_1
+   ->  Seq Scan on atest12 atest12_1  (cost=0.00..170.00 rows=4 width=8)
          Filter: (b <<< 5)
 (8 rows)
 
diff -U3 
/home/vagrant/postgresql/src/test/regress/expected/incremental_sort.out 
/home/vagrant/postgresql/src/test/regress/results/incremental_sort.out
--- /home/vagrant/postgresql/src/test/regress/expected/incremental_sort.out     
2022-04-15 22:58:14.776668287 +0000
+++ /home/vagrant/postgresql/src/test/regress/results/incremental_sort.out      
2022-04-29 00:12:55.196444456 +0000
@@ -1478,22 +1478,22 @@
 
 -- Incremental sort vs. set operations with varno 0
 set enable_hashagg to off;
-explain (costs off) select * from t union select * from t order by 1,3;
-                        QUERY PLAN                        
-----------------------------------------------------------
- Incremental Sort
+explain  select * from t union select * from t order by 1,3;
+                                            QUERY PLAN                         
                    
+---------------------------------------------------------------------------------------------------
+ Incremental Sort  (cost=29425.50..47127.57 rows=120000 width=12)
    Sort Key: t.a, t.c
    Presorted Key: t.a
-   ->  Unique
-         ->  Sort
+   ->  Unique  (cost=29344.85..30544.85 rows=120000 width=12)
+         ->  Sort  (cost=29344.85..29644.85 rows=120000 width=12)
                Sort Key: t.a, t.b, t.c
-               ->  Append
-                     ->  Gather
+               ->  Append  (cost=0.00..2950.00 rows=120000 width=12)
+                     ->  Gather  (cost=0.00..575.00 rows=60000 width=12)
                            Workers Planned: 2
-                           ->  Parallel Seq Scan on t
-                     ->  Gather
+                           ->  Parallel Seq Scan on t  (cost=0.00..575.00 
rows=25000 width=12)
+                     ->  Gather  (cost=0.00..575.00 rows=60000 width=12)
                            Workers Planned: 2
-                           ->  Parallel Seq Scan on t t_1
+                           ->  Parallel Seq Scan on t t_1  (cost=0.00..575.00 
rows=25000 width=12)
 (13 rows)
 
 -- Full sort, not just incremental sort can be pushed below a gather merge path
diff -U3 /home/vagrant/postgresql/src/test/regress/expected/subselect.out 
/home/vagrant/postgresql/src/test/regress/results/subselect.out
--- /home/vagrant/postgresql/src/test/regress/expected/subselect.out    
2022-04-11 02:01:56.846066150 +0000
+++ /home/vagrant/postgresql/src/test/regress/results/subselect.out     
2022-04-29 00:16:18.988436098 +0000
@@ -1298,31 +1298,35 @@
 --
 -- Check for incorrect optimization when IN subquery contains a SRF
 --
-explain (verbose, costs off)
+explain (verbose)
 select * from int4_tbl o where (f1, f1) in
   (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1);
-                            QUERY PLAN                             
--------------------------------------------------------------------
- Nested Loop Semi Join
+                                                QUERY PLAN                     
                            
+-----------------------------------------------------------------------------------------------------------
+ Hash Join  (cost=9.30..10.37 rows=1 width=4)
    Output: o.f1
-   Join Filter: (o.f1 = "ANY_subquery".f1)
-   ->  Seq Scan on public.int4_tbl o
+   Inner Unique: true
+   Hash Cond: (o.f1 = "ANY_subquery".f1)
+   ->  Seq Scan on public.int4_tbl o  (cost=0.00..1.05 rows=5 width=4)
          Output: o.f1
-   ->  Materialize
+   ->  Hash  (cost=9.29..9.29 rows=1 width=8)
          Output: "ANY_subquery".f1, "ANY_subquery".g
-         ->  Subquery Scan on "ANY_subquery"
+         ->  HashAggregate  (cost=9.28..9.29 rows=1 width=8)
                Output: "ANY_subquery".f1, "ANY_subquery".g
-               Filter: ("ANY_subquery".f1 = "ANY_subquery".g)
-               ->  Result
-                     Output: i.f1, ((generate_series(1, 50)) / 10)
-                     ->  ProjectSet
-                           Output: generate_series(1, 50), i.f1
-                           ->  HashAggregate
-                                 Output: i.f1
-                                 Group Key: i.f1
-                                 ->  Seq Scan on public.int4_tbl i
+               Group Key: "ANY_subquery".f1, "ANY_subquery".g
+               ->  Subquery Scan on "ANY_subquery"  (cost=1.06..9.28 rows=250 
width=8)
+                     Output: "ANY_subquery".f1, "ANY_subquery".g
+                     Filter: ("ANY_subquery".f1 = "ANY_subquery".g)
+                     ->  Result  (cost=1.06..6.15 rows=250 width=8)
+                           Output: i.f1, ((generate_series(1, 50)) / 10)
+                           ->  ProjectSet  (cost=1.06..2.40 rows=250 width=8)
+                                 Output: generate_series(1, 50), i.f1
+                                 ->  HashAggregate  (cost=1.06..1.11 rows=5 
width=4)
                                        Output: i.f1
-(19 rows)
+                                       Group Key: i.f1
+                                       ->  Seq Scan on public.int4_tbl i  
(cost=0.00..1.05 rows=5 width=4)
+                                             Output: i.f1
+(23 rows)
 
 select * from int4_tbl o where (f1, f1) in
   (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1);
diff -U3 /home/vagrant/postgresql/src/test/regress/expected/privileges.out 
/home/vagrant/postgresql/src/test/regress/results/privileges.out
--- /home/vagrant/postgresql/src/test/regress/expected/privileges.out   
2022-04-15 22:58:14.784668286 +0000
+++ /home/vagrant/postgresql/src/test/regress/results/privileges.out    
2022-04-29 00:16:26.396435795 +0000
@@ -363,19 +363,20 @@
 (6 rows)
 
 -- But a security barrier view isolates the leaky operator.
-EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y
+EXPLAIN SELECT * FROM atest12sbv x, atest12sbv y
   WHERE x.a = y.b and abs(y.a) <<< 5;
-             QUERY PLAN              
--------------------------------------
- Nested Loop
-   Join Filter: (atest12_1.a = y.b)
-   ->  Subquery Scan on y
-         Filter: (abs(y.a) <<< 5)
-         ->  Seq Scan on atest12
-               Filter: (b <<< 5)
-   ->  Seq Scan on atest12 atest12_1
+                                     QUERY PLAN                                
      
+-------------------------------------------------------------------------------------
+ Nested Loop  (cost=0.00..340.35 rows=1 width=16)
+   Join Filter: (atest12.a = y.b)
+   ->  Seq Scan on atest12  (cost=0.00..170.00 rows=4 width=8)
          Filter: (b <<< 5)
-(8 rows)
+   ->  Materialize  (cost=0.00..170.08 rows=4 width=8)
+         ->  Subquery Scan on y  (cost=0.00..170.06 rows=4 width=8)
+               Filter: (abs(y.a) <<< 5)
+               ->  Seq Scan on atest12 atest12_1  (cost=0.00..170.00 rows=4 
width=8)
+                     Filter: (b <<< 5)
+(9 rows)
 
 -- Now regress_priv_user1 grants sufficient access to regress_priv_user2.
 SET SESSION AUTHORIZATION regress_priv_user1;
diff -U3 
/home/vagrant/postgresql/src/test/regress/expected/incremental_sort.out 
/home/vagrant/postgresql/src/test/regress/results/incremental_sort.out
--- /home/vagrant/postgresql/src/test/regress/expected/incremental_sort.out     
2022-04-15 22:58:14.776668287 +0000
+++ /home/vagrant/postgresql/src/test/regress/results/incremental_sort.out      
2022-04-29 00:16:32.376435549 +0000
@@ -1478,23 +1478,21 @@
 
 -- Incremental sort vs. set operations with varno 0
 set enable_hashagg to off;
-explain (costs off) select * from t union select * from t order by 1,3;
-                        QUERY PLAN                        
-----------------------------------------------------------
- Incremental Sort
+explain  select * from t union select * from t order by 1,3;
+                                            QUERY PLAN                         
                    
+---------------------------------------------------------------------------------------------------
+ Incremental Sort  (cost=29075.50..46777.57 rows=120000 width=12)
    Sort Key: t.a, t.c
    Presorted Key: t.a
-   ->  Unique
-         ->  Sort
+   ->  Unique  (cost=28994.85..30194.85 rows=120000 width=12)
+         ->  Sort  (cost=28994.85..29294.85 rows=120000 width=12)
                Sort Key: t.a, t.b, t.c
-               ->  Append
-                     ->  Gather
-                           Workers Planned: 2
-                           ->  Parallel Seq Scan on t
-                     ->  Gather
-                           Workers Planned: 2
-                           ->  Parallel Seq Scan on t t_1
-(13 rows)
+               ->  Gather  (cost=0.00..2600.00 rows=120000 width=12)
+                     Workers Planned: 2
+                     ->  Parallel Append  (cost=0.00..2600.00 rows=50000 
width=12)
+                           ->  Parallel Seq Scan on t  (cost=0.00..575.00 
rows=25000 width=12)
+                           ->  Parallel Seq Scan on t t_1  (cost=0.00..575.00 
rows=25000 width=12)
+(11 rows)
 
 -- Full sort, not just incremental sort can be pushed below a gather merge path
 -- by generate_useful_gather_paths.

Reply via email to