Re: [HACKERS] MERGE SQL Statement for PG11

On Sat, Mar 24, 2018 at 1:36 AM, Peter Geoghegan <pg(at)bowt(dot)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