Case expression pushdown

From: Alexander Pyhalov <a(dot)pyhalov(at)postgrespro(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Case expression pushdown
Date: 2021-06-09 11:55:19
Message-ID: fda09032e90d85d9b726a41e03f9097f@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi.

This patch allows pushing case expressions to foreign servers, so that
more types of updates could be executed directly.

For example, without patch:

EXPLAIN (VERBOSE, COSTS OFF)
UPDATE ft2 d SET c2 = CASE WHEN c2 > 0 THEN c2 ELSE 0 END
WHERE c1 > 1000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Update on public.ft2 d
Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1
-> Foreign Scan on public.ft2 d
Output: CASE WHEN (c2 > 0) THEN c2 ELSE 0 END, ctid, d.*
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM
"S 1"."T 1" WHERE (("C 1" > 1000)) FOR UPDATE

EXPLAIN (VERBOSE, COSTS OFF)
UPDATE ft2 d SET c2 = CASE WHEN c2 > 0 THEN c2 ELSE 0 END
WHERE c1 > 1000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Update on public.ft2 d
-> Foreign Update on public.ft2 d
Remote SQL: UPDATE "S 1"."T 1" SET c2 = (CASE WHEN (c2 > 0)
THEN c2 ELSE 0 END) WHERE (("C 1" > 1000))

--
Best regards,
Alexander Pyhalov,
Postgres Professional

Attachment Content-Type Size
0001-Allow-pushing-CASE-expression-to-foreign-server.patch text/x-diff 9.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2021-06-09 11:57:35 Re: Decoding of two-phase xacts missing from CREATE_REPLICATION_SLOT command
Previous Message Nitin Jadhav 2021-06-09 11:39:54 Re: when the startup process doesn't