Representation of SubPlan testexpr in EXPLAIN

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Representation of SubPlan testexpr in EXPLAIN
Date: 2021-08-24 07:21:48
Message-ID: de9a504e-0470-843d-72d6-3abc7517a448@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

EXPLAIN command doesn't show testexpr. Sometimes it is not easy to
understand a query plan. That I mean:

CREATE TABLE a (x integer, y integer);
EXPLAIN (COSTS OFF, VERBOSE) SELECT x, y FROM a upper
WHERE y IN (SELECT y FROM a WHERE upper.y = x);
EXPLAIN (COSTS OFF, VERBOSE) SELECT x, y FROM a upper
WHERE x+y IN (SELECT y FROM a WHERE upper.y = x);

These two explains have the same representation:
Seq Scan on public.a upper
Output: upper.x, upper.y
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on public.a
Output: a.y
Filter: (upper.y = a.x)

It is a bit annoying when you don't have original query or don't trust
competence of a user who sent you this explain.
In attachment - patch which solves this problem. I'm not completely sure
that this option really needed and patch presents a proof of concept only.

--
regards,
Andrey Lepikhov
Postgres Professional

Attachment Content-Type Size
0001-Improve-EXPLAIN-presentation.patch text/plain 22.0 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message 蔡梦娟 (玊于) 2021-08-24 07:25:08 回复:Queries that should be canceled will get stuck on secure_write function
Previous Message Amit Kapila 2021-08-24 06:56:02 Re: Separate out FileSet from SharedFileSet (was Re: pgsql: pgstat: Bring up pgstat in BaseInit() to fix uninitialized use o)