Re: [HACKERS] MERGE SQL Statement for PG11 - Mailing list pgsql-hackers
| From | Pavan Deolasee |
|---|---|
| Subject | Re: [HACKERS] MERGE SQL Statement for PG11 |
| Date | |
| Msg-id | CABOikdNK-d9jQs0ecQQFQViY+f6G5b-b_-iQpHkNAcwQtWFyFQ@mail.gmail.com Whole thread Raw |
| In response to | Re: [HACKERS] MERGE SQL Statement for PG11 (Peter Geoghegan <pg@bowt.ie>) |
| Responses |
Re: [HACKERS] MERGE SQL Statement for PG11
(Peter Geoghegan <pg@bowt.ie>)
|
| List | pgsql-hackers |
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
PostgreSQL Development, 24x7 Support, Training & Services
pgsql-hackers by date: