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)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 31919fda8c..8b8ca91e25 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -185,6 +185,7 @@ static void appendAggOrderBy(List *orderList, List *targetList,
static void appendFunctionName(Oid funcid, deparse_expr_cxt *context);
static Node *deparseSortGroupClause(Index ref, List *tlist, bool force_colno,
deparse_expr_cxt *context);
+static void deparseCaseExpr(CaseExpr *node, deparse_expr_cxt *context);
/*
* Helper functions
@@ -796,6 +797,53 @@ foreign_expr_walker(Node *node,
state = FDW_COLLATE_UNSAFE;
}
break;
+ case T_CaseTestExpr:
+ {
+CaseTestExpr *c = (CaseTestExpr *) node;
+
+/*
+ * If the expression has nondefault collation, either it's of a
+ * non-builtin type, or it reflects folding of a CollateExpr.
+ * It's unsafe to send to the remote unless it's used in a
+ * non-collation-sensitive context.
+ */
+collation = c->collation;
+if
