Re: [HACKERS] MERGE SQL Statement for PG11

On Sat, Mar 24, 2018 at 1:36 AM, Peter Geoghegan <pg@bowt.ie> wrote:

>
> Fair enough. Attached patch shows what I'm on about. This should be
> applied on top of 0001_merge_v23e_onconflict_work.patch +
> 0002_merge_v23e_main.patch. I'm not expecting an authorship credit for
> posting this patch.
>

Thanks for the patch. I will study and integrate this into the main patch.


>
> One thing that the test output shows that is interesting is that there
> is never a "SubPlan 1" or "InitPlan 1" in EXPLAIN output -- it seems
> to always start at "SubPlan 2". This probably has nothing to do with
> CTEs in particular. I didn't notice this before now, although there
> were no existing tests of EXPLAIN in the patch that show subplans or
> initplans.
>

This query e.g. correctly starts at InitPlan 1

postgres=# EXPLAIN MERGE INTO m USING (SELECT 1 a, 'val' b) s ON m.k = s.a
WHEN NOT MATCHED THEN INSERT VALUES ((select count(*) from pg_class), s.b);
                               QUERY PLAN
-------------------------------------------------------------------------
 Merge on m  (cost=16.30..43.83 rows=6 width=106)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=16.26..16.27 rows=1 width=8)
           ->  Seq Scan on pg_class  (cost=0.00..15.41 rows=341 width=0)
   ->  Hash Right Join  (cost=0.03..27.55 rows=6 width=106)
         Hash Cond: (m_1.k = s.a)
         ->  Seq Scan on m m_1  (cost=0.00..22.70 rows=1270 width=14)
         ->  Hash  (cost=0.02..0.02 rows=1 width=96)
               ->  Subquery Scan on s  (cost=0.00..0.02 rows=1 width=96)
                     ->  Result  (cost=0.00..0.01 rows=1 width=36)
(10 rows)



>
> Is this somehow related to the issue of using two RTEs for the target
> relation? That's certainly why we always see unaliased target table
> "m" with the alias "m_1" in EXPLAIN output, so I would not be
> surprised if it caused another EXPLAIN issue.
>

I don't think it's related to using two RTEs. The following EXPLAIN for a
regular UPDATE query also shows a SubPlan starting at 2. I think it's just
to do with how planner assigns the plan_id.

postgres=# EXPLAIN WITH cte_basic AS (SELECT 1 a, 'cte_basic val' b) UPDATE
m SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a =
m.k LIMIT 1) ;
                                  QUERY PLAN

------------------------------------------------------------------------------
 Update on m  (cost=0.01..54.46 rows=1270 width=42)
   CTE cte_basic
     ->  Result  (cost=0.00..0.01 rows=1 width=36)
   ->  Seq Scan on m  (cost=0.00..54.45 rows=1270 width=42)
         SubPlan 2
           ->  Limit  (cost=0.00..0.02 rows=1 width=32)
                 ->  CTE Scan on cte_basic  (cost=0.00..0.02 rows=1
width=32)
                       Filter: (a = m.k)
(8 rows)

A quick gdb tracing shows that the CTE itself is assigned plan_id 1 and the
SubPlan then gets plan_id 2. I can investigate further, but given that we
see a similar behaviour with regular UPDATE, I don't think it's worth.

Thanks,
Pavan

-- 
 Pavan Deolasee                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services