[PATCH][postgres_fdw] Add push down of CASE WHEN clauses - Mailing list pgsql-hackers

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)