Thread: [PATCH][postgres_fdw] Add push down of CASE WHEN clauses
Hi,
I have noticed that postgres_fdw do not push down the CASE WHEN clauses. In the following case this normal:
contrib_regression=# EXPLAIN (ANALYZE, VERBOSE) SELECT (CASE WHEN mod(c1, 4) = 0 THEN 1 ELSE 2 END) FROM ft1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1 (cost=100.00..146.00 rows=1000 width=4) (actual time=0.306..0.844 rows=822 loops=1)
Output: CASE WHEN (mod(c1, 4) = 0) THEN 1 ELSE 2 END
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
Planning Time: 0.139 ms
Execution Time: 1.057 ms
(5 rows)
but in these other cases this is a performances killer, all records are fetched
contrib_regression=# EXPLAIN (ANALYZE, VERBOSE) SELECT sum(CASE WHEN mod(c1, 4) = 0 THEN 1 ELSE 2 END) FROM ft1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=148.50..148.51 rows=1 width=8) (actual time=1.421..1.422 rows=1 loops=1)
Output: sum(CASE WHEN (mod(c1, 4) = 0) THEN 1 ELSE 2 END)
-> Foreign Scan on public.ft1 (cost=100.00..141.00 rows=1000 width=4) (actual time=0.694..1.366 rows=822 loops=1)
Output: c1
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
Planning Time: 1.531 ms
Execution Time: 3.901 ms
(7 rows)
contrib_regression=# EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM ft1 WHERE c1 > (CASE WHEN mod(c1, 4) = 0 THEN 1 ELSE 100 END);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1 (cost=100.00..148.48 rows=333 width=47) (actual time=0.763..3.003 rows=762 loops=1)
Output: c1, c2, c3, c4, c5, c6, c7, c8
Filter: (ft1.c1 > CASE WHEN (mod(ft1.c1, 4) = 0) THEN 1 ELSE 100 END)
Rows Removed by Filter: 60
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
Planning Time: 0.584 ms
Execution Time: 3.392 ms
(7 rows)
The attached patch adds push down of CASE WHEN clauses. Queries above have the following plans when this patch is applied:
contrib_regression=# EXPLAIN (ANALYZE, VERBOSE) SELECT sum(CASE WHEN mod(c1, 4) = 0 THEN 1 ELSE 2 END) FROM ft1;
QUERY PLAN
----------------------------------------------------------------------------------------------
Foreign Scan (cost=107.50..128.53 rows=1 width=8) (actual time=2.022..2.024 rows=1 loops=1)
Output: (sum(CASE WHEN (mod(c1, 4) = 0) THEN 1 ELSE 2 END))
Relations: Aggregate on (public.ft1)
Remote SQL: SELECT sum(CASE WHEN (mod("C 1", 4) = 0) THEN 1 ELSE 2 END) FROM "S 1"."T 1"
Planning Time: 0.252 ms
Execution Time: 2.684 ms
(6 rows)
contrib_regression=# EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM ft1 WHERE c1 > (CASE WHEN mod(c1, 4) = 0 THEN 1 ELSE 100 END);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
----------------------
Foreign Scan on public.ft1 (cost=100.00..135.16 rows=333 width=47) (actual time=1.797..3.463 rows=762 loops=1)
Output: c1, c2, c3, c4, c5, c6, c7, c8
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" > CASE WHEN (mod("C 1", 4) = 0)
THEN 1 ELSE 100 END))
Planning Time: 0.745 ms
Execution Time: 3.860 ms
(5 rows)
I don't see a good reason to never push the CASE WHEN clause but perhaps I'm missing something, any though?
Best regards,
-- Gilles Darold MigOps Inc (http://migops.com)
Attachment
On Wed, 7 Jul 2021 at 10:18, Gilles Darold <gilles@migops.com> wrote: > I have noticed that postgres_fdw do not push down the CASE WHEN clauses. In the following case this normal: This looks very similar to [1] which is in the current commitfest. Are you able to look over that patch and check to ensure you're not doing anything extra that the other patch isn't. If so, then likely the best way to progress would be for you to test and review that patch. David [1] https://commitfest.postgresql.org/33/3171/
Le 07/07/2021 à 06:59, David Rowley a écrit : > On Wed, 7 Jul 2021 at 10:18, Gilles Darold <gilles@migops.com> wrote: >> I have noticed that postgres_fdw do not push down the CASE WHEN clauses. In the following case this normal: > This looks very similar to [1] which is in the current commitfest. > > Are you able to look over that patch and check to ensure you're not > doing anything extra that the other patch isn't. If so, then likely > the best way to progress would be for you to test and review that > patch. > > David > > [1] https://commitfest.postgresql.org/33/3171/ Strange I have searched the commitfest yesterday but without success, this is clearly a duplicate. Anyway, thanks for the pointer and yes I will review Alexander's patch as I know the subject now :-) Best regards -- Gilles Darold MigOps Inc (https://migops.com/)