Re: Case expression pushdown - Mailing list pgsql-hackers

From Ashutosh Bapat
Subject Re: Case expression pushdown
Date
Msg-id CAExHW5skEf2d3fA0LXubQc2wD2NFoqgWKsbNS_SzHFtRARkyow@mail.gmail.com
Whole thread Raw
In response to Case expression pushdown  (Alexander Pyhalov <a.pyhalov@postgrespro.ru>)
Responses Re: Case expression pushdown
List pgsql-hackers
Looks quite useful to me. Can you please add this to the next commitfest?

On Wed, Jun 9, 2021 at 5:25 PM Alexander Pyhalov
<a.pyhalov@postgrespro.ru> wrote:
>
> 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



-- 
Best Wishes,
Ashutosh Bapat



pgsql-hackers by date:

Previous
From: Adam Brusselback
Date:
Subject: Re: [Proposal] Fully WAL logged CREATE DATABASE - No Checkpoints
Next
From: David Rowley
Date:
Subject: Re: [PATCH] expand the units that pg_size_pretty supports on output