public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 1/3] postgres_fdw: Perform UPPERREL_ORDERED step remotely 3+ messages / 3 participants [nested] [flat]
* [PATCH 1/3] postgres_fdw: Perform UPPERREL_ORDERED step remotely @ 2019-03-20 10:19 Etsuro Fujita <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Etsuro Fujita @ 2019-03-20 10:19 UTC (permalink / raw) --- contrib/postgres_fdw/deparse.c | 28 +- .../postgres_fdw/expected/postgres_fdw.out | 167 +++----- contrib/postgres_fdw/postgres_fdw.c | 388 ++++++++++++++++-- contrib/postgres_fdw/postgres_fdw.h | 12 +- 4 files changed, 459 insertions(+), 136 deletions(-) diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 92a0ab6da5..97dd07bee8 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -167,7 +167,8 @@ static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod, static void deparseSelectSql(List *tlist, bool is_subquery, List **retrieved_attrs, deparse_expr_cxt *context); static void deparseLockingClause(deparse_expr_cxt *context); -static void appendOrderByClause(List *pathkeys, deparse_expr_cxt *context); +static void appendOrderByClause(List *pathkeys, bool has_final_sort, + deparse_expr_cxt *context); static void appendConditions(List *exprs, deparse_expr_cxt *context); static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel, bool use_alias, @@ -929,8 +930,8 @@ build_tlist_to_deparse(RelOptInfo *foreignrel) void deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel, List *tlist, List *remote_conds, List *pathkeys, - bool is_subquery, List **retrieved_attrs, - List **params_list) + bool has_final_sort, bool is_subquery, + List **retrieved_attrs, List **params_list) { deparse_expr_cxt context; PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) rel->fdw_private; @@ -985,7 +986,7 @@ deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel, /* Add ORDER BY clause if we found any useful pathkeys */ if (pathkeys) - appendOrderByClause(pathkeys, &context); + appendOrderByClause(pathkeys, has_final_sort, &context); /* Add any necessary FOR UPDATE/SHARE. */ deparseLockingClause(&context); @@ -1590,7 +1591,7 @@ deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel, /* Deparse the subquery representing the relation. */ appendStringInfoChar(buf, '('); deparseSelectStmtForRel(buf, root, foreignrel, NIL, - fpinfo->remote_conds, NIL, true, + fpinfo->remote_conds, NIL, false, true, &retrieved_attrs, params_list); appendStringInfoChar(buf, ')'); @@ -3109,7 +3110,8 @@ appendGroupByClause(List *tlist, deparse_expr_cxt *context) * base relation are obtained and deparsed. */ static void -appendOrderByClause(List *pathkeys, deparse_expr_cxt *context) +appendOrderByClause(List *pathkeys, bool has_final_sort, + deparse_expr_cxt *context) { ListCell *lcell; int nestlevel; @@ -3126,7 +3128,19 @@ appendOrderByClause(List *pathkeys, deparse_expr_cxt *context) PathKey *pathkey = lfirst(lcell); Expr *em_expr; - em_expr = find_em_expr_for_rel(pathkey->pk_eclass, baserel); + if (has_final_sort) + { + /* + * By construction, context->foreignrel is the input relation to + * the final sort. + */ + em_expr = find_em_expr_for_input_target(context->root, + pathkey->pk_eclass, + context->foreignrel->reltarget); + } + else + em_expr = find_em_expr_for_rel(pathkey->pk_eclass, baserel); + Assert(em_expr != NULL); appendStringInfoString(buf, delim); diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 42108bd3d4..592dd3e05f 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2552,18 +2552,13 @@ DROP ROLE regress_view_owner; -- Simple aggregates explain (verbose, costs off) select count(c6), sum(c1), avg(c1), min(c2), max(c1), stddev(c2), sum(c1) * (random() <= 1)::int as sum2 from ft1 where c2 < 5 group by c2 order by 1, 2; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- - Result + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan Output: (count(c6)), (sum(c1)), (avg(c1)), (min(c2)), (max(c1)), (stddev(c2)), ((sum(c1)) * ((random() <= '1'::double precision))::integer), c2 - -> Sort - Output: (count(c6)), (sum(c1)), (avg(c1)), (min(c2)), (max(c1)), (stddev(c2)), c2 - Sort Key: (count(ft1.c6)), (sum(ft1.c1)) - -> Foreign Scan - Output: (count(c6)), (sum(c1)), (avg(c1)), (min(c2)), (max(c1)), (stddev(c2)), c2 - Relations: Aggregate on (public.ft1) - Remote SQL: SELECT count(c6), sum("C 1"), avg("C 1"), min(c2), max("C 1"), stddev(c2), c2 FROM "S 1"."T 1" WHERE ((c2 < 5)) GROUP BY 7 -(9 rows) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT count(c6), sum("C 1"), avg("C 1"), min(c2), max("C 1"), stddev(c2), c2 FROM "S 1"."T 1" WHERE ((c2 < 5)) GROUP BY 7 ORDER BY count(c6) ASC NULLS LAST, sum("C 1") ASC NULLS LAST +(4 rows) select count(c6), sum(c1), avg(c1), min(c2), max(c1), stddev(c2), sum(c1) * (random() <= 1)::int as sum2 from ft1 where c2 < 5 group by c2 order by 1, 2; count | sum | avg | min | max | stddev | sum2 @@ -2621,16 +2616,13 @@ select sum(t1.c1), count(t2.c1) from ft1 t1 inner join ft2 t2 on (t1.c1 = t2.c1) -- GROUP BY clause having expressions explain (verbose, costs off) select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; - QUERY PLAN ---------------------------------------------------------------------------------------- - Sort + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ + Foreign Scan Output: ((c2 / 2)), ((sum(c2) * (c2 / 2))) - Sort Key: ((ft1.c2 / 2)) - -> Foreign Scan - Output: ((c2 / 2)), ((sum(c2) * (c2 / 2))) - Relations: Aggregate on (public.ft1) - Remote SQL: SELECT (c2 / 2), (sum(c2) * (c2 / 2)) FROM "S 1"."T 1" GROUP BY 1 -(7 rows) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT (c2 / 2), (sum(c2) * (c2 / 2)) FROM "S 1"."T 1" GROUP BY 1 ORDER BY (c2 / 2) ASC NULLS LAST +(4 rows) select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; ?column? | ?column? @@ -2645,18 +2637,15 @@ select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; -- Aggregates in subquery are pushed down. explain (verbose, costs off) select count(x.a), sum(x.a) from (select c2 a, sum(c1) b from ft1 group by c2, sqrt(c1) order by 1, 2) x; - QUERY PLAN ---------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------- Aggregate Output: count(ft1.c2), sum(ft1.c2) - -> Sort + -> Foreign Scan Output: ft1.c2, (sum(ft1.c1)), (sqrt((ft1.c1)::double precision)) - Sort Key: ft1.c2, (sum(ft1.c1)) - -> Foreign Scan - Output: ft1.c2, (sum(ft1.c1)), (sqrt((ft1.c1)::double precision)) - Relations: Aggregate on (public.ft1) - Remote SQL: SELECT c2, sum("C 1"), sqrt("C 1") FROM "S 1"."T 1" GROUP BY 1, 3 -(9 rows) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT c2, sum("C 1"), sqrt("C 1") FROM "S 1"."T 1" GROUP BY 1, 3 ORDER BY c2 ASC NULLS LAST, sum("C 1") ASC NULLS LAST +(6 rows) select count(x.a), sum(x.a) from (select c2 a, sum(c1) b from ft1 group by c2, sqrt(c1) order by 1, 2) x; count | sum @@ -2742,16 +2731,13 @@ select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2 -- Also, ORDER BY contains an aggregate function explain (verbose, costs off) select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1); - QUERY PLAN ------------------------------------------------------------------------------------------------ - Sort + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------- + Foreign Scan Output: c2, c2, (sum(c1)) - Sort Key: (sum(ft1.c1)) - -> Foreign Scan - Output: c2, c2, (sum(c1)) - Relations: Aggregate on (public.ft1) - Remote SQL: SELECT c2, c2, sum("C 1") FROM "S 1"."T 1" WHERE ((c2 > 6)) GROUP BY 1, 2 -(7 rows) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT c2, c2, sum("C 1") FROM "S 1"."T 1" WHERE ((c2 > 6)) GROUP BY 1, 2 ORDER BY sum("C 1") ASC NULLS LAST +(4 rows) select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1); c2 | c2 @@ -2764,16 +2750,13 @@ select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1); -- Testing HAVING clause shippability explain (verbose, costs off) select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 order by c2; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------- - Sort + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------ + Foreign Scan Output: c2, (sum(c1)) - Sort Key: ft2.c2 - -> Foreign Scan - Output: c2, (sum(c1)) - Relations: Aggregate on (public.ft2) - Remote SQL: SELECT c2, sum("C 1") FROM "S 1"."T 1" GROUP BY 1 HAVING ((avg("C 1") < 500::numeric)) AND ((sum("C 1") < 49800)) -(7 rows) + Relations: Aggregate on (public.ft2) + Remote SQL: SELECT c2, sum("C 1") FROM "S 1"."T 1" GROUP BY 1 HAVING ((avg("C 1") < 500::numeric)) AND ((sum("C 1") < 49800)) ORDER BY c2 ASC NULLS LAST +(4 rows) select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 order by c2; c2 | sum @@ -2823,16 +2806,13 @@ select sum(c1) from ft1 group by c2 having avg(c1 * (random() <= 1)::int) > 100 -- ORDER BY within aggregate, same column used to order explain (verbose, costs off) select array_agg(c1 order by c1) from ft1 where c1 < 100 group by c2 order by 1; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- - Sort + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan Output: (array_agg(c1 ORDER BY c1)), c2 - Sort Key: (array_agg(ft1.c1 ORDER BY ft1.c1)) - -> Foreign Scan - Output: (array_agg(c1 ORDER BY c1)), c2 - Relations: Aggregate on (public.ft1) - Remote SQL: SELECT array_agg("C 1" ORDER BY "C 1" ASC NULLS LAST), c2 FROM "S 1"."T 1" WHERE (("C 1" < 100)) GROUP BY 2 -(7 rows) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT array_agg("C 1" ORDER BY "C 1" ASC NULLS LAST), c2 FROM "S 1"."T 1" WHERE (("C 1" < 100)) GROUP BY 2 ORDER BY array_agg("C 1" ORDER BY "C 1" ASC NULLS LAST) ASC NULLS LAST +(4 rows) select array_agg(c1 order by c1) from ft1 where c1 < 100 group by c2 order by 1; array_agg @@ -2869,16 +2849,13 @@ select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50; -- DISTINCT within aggregate explain (verbose, costs off) select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Sort + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Foreign Scan Output: (array_agg(DISTINCT (t1.c1 % 5))), ((t2.c1 % 3)) - Sort Key: (array_agg(DISTINCT (t1.c1 % 5))) - -> Foreign Scan - Output: (array_agg(DISTINCT (t1.c1 % 5))), ((t2.c1 % 3)) - Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) - Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5)), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY 2 -(7 rows) + Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) + Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5)), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY 2 ORDER BY array_agg(DISTINCT (r1.c1 % 5)) ASC NULLS LAST +(4 rows) select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; array_agg @@ -2890,16 +2867,13 @@ select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2 -- DISTINCT combined with ORDER BY within aggregate explain (verbose, costs off) select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Sort + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5))), ((t2.c1 % 3)) - Sort Key: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5))) - -> Foreign Scan - Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5))), ((t2.c1 % 3)) - Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) - Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5) ORDER BY ((r1.c1 % 5)) ASC NULLS LAST), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY 2 -(7 rows) + Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) + Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5) ORDER BY ((r1.c1 % 5)) ASC NULLS LAST), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY 2 ORDER BY array_agg(DISTINCT (r1.c1 % 5) ORDER BY ((r1.c1 % 5)) ASC NULLS LAST) ASC NULLS LAST +(4 rows) select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; array_agg @@ -2910,16 +2884,13 @@ select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft explain (verbose, costs off) select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Sort + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Foreign Scan Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5) DESC NULLS LAST)), ((t2.c1 % 3)) - Sort Key: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5) DESC NULLS LAST)) - -> Foreign Scan - Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5) DESC NULLS LAST)), ((t2.c1 % 3)) - Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) - Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5) ORDER BY ((r1.c1 % 5)) DESC NULLS LAST), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY 2 -(7 rows) + Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) + Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5) ORDER BY ((r1.c1 % 5)) DESC NULLS LAST), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY 2 ORDER BY array_agg(DISTINCT (r1.c1 % 5) ORDER BY ((r1.c1 % 5)) DESC NULLS LAST) ASC NULLS LAST +(4 rows) select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; array_agg @@ -2931,16 +2902,13 @@ select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 -- FILTER within aggregate explain (verbose, costs off) select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------- - Sort + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan Output: (sum(c1) FILTER (WHERE ((c1 < 100) AND (c2 > 5)))), c2 - Sort Key: (sum(ft1.c1) FILTER (WHERE ((ft1.c1 < 100) AND (ft1.c2 > 5)))) - -> Foreign Scan - Output: (sum(c1) FILTER (WHERE ((c1 < 100) AND (c2 > 5)))), c2 - Relations: Aggregate on (public.ft1) - Remote SQL: SELECT sum("C 1") FILTER (WHERE (("C 1" < 100) AND (c2 > 5))), c2 FROM "S 1"."T 1" GROUP BY 2 -(7 rows) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT sum("C 1") FILTER (WHERE (("C 1" < 100) AND (c2 > 5))), c2 FROM "S 1"."T 1" GROUP BY 2 ORDER BY sum("C 1") FILTER (WHERE (("C 1" < 100) AND (c2 > 5))) ASC NULLS LAST +(4 rows) select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last; sum @@ -3339,16 +3307,13 @@ select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x w -- FULL join with IS NULL check in HAVING explain (verbose, costs off) select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null order by 1 nulls last, 2; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Sort + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan Output: (avg(t1.c1)), (sum(t2.c1)), t2.c1 - Sort Key: (avg(t1.c1)), (sum(t2.c1)) - -> Foreign Scan - Output: (avg(t1.c1)), (sum(t2.c1)), t2.c1 - Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) - Remote SQL: SELECT avg(r1.c1), sum(r2.c1), r2.c1 FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) GROUP BY 3 HAVING ((((avg(r1.c1) IS NULL) AND (sum(r2.c1) < 10)) OR (sum(r2.c1) IS NULL))) -(7 rows) + Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) + Remote SQL: SELECT avg(r1.c1), sum(r2.c1), r2.c1 FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) GROUP BY 3 HAVING ((((avg(r1.c1) IS NULL) AND (sum(r2.c1) < 10)) OR (sum(r2.c1) IS NULL))) ORDER BY avg(r1.c1) ASC NULLS LAST, sum(r2.c1) ASC NULLS LAST +(4 rows) select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null order by 1 nulls last, 2; avg | sum diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 2f387fac42..4208bf6baa 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -246,6 +246,25 @@ typedef struct PgFdwAnalyzeState MemoryContext temp_cxt; /* context for per-tuple temporary data */ } PgFdwAnalyzeState; +/* + * This enum describes what's kept in the fdw_private list for a ForeignPath. + * We store: + * + * 1) Boolean flag showing if the remote query has the final sort + */ +enum FdwPathPrivateIndex +{ + /* has-final-sort flag (as an integer Value node) */ + FdwPathPrivateHasFinalSort +}; + +/* Struct for extra information passed to estimate_path_cost_size() */ +typedef struct +{ + PathTarget *target; + bool has_final_sort; +} PgFdwPathExtraData; + /* * Identify the attribute where data conversion fails. */ @@ -368,6 +387,7 @@ static void estimate_path_cost_size(PlannerInfo *root, RelOptInfo *foreignrel, List *param_join_conds, List *pathkeys, + PgFdwPathExtraData *fpextra, double *p_rows, int *p_width, Cost *p_startup_cost, Cost *p_total_cost); static void get_remote_estimate(const char *sql, @@ -376,6 +396,12 @@ static void get_remote_estimate(const char *sql, int *width, Cost *startup_cost, Cost *total_cost); +static void adjust_foreign_grouping_path_cost(PlannerInfo *root, + List *pathkeys, + double retrieved_rows, + double width, + Cost *p_startup_cost, + Cost *p_run_cost); static bool ec_member_matches_foreign(PlannerInfo *root, RelOptInfo *rel, EquivalenceClass *ec, EquivalenceMember *em, void *arg); @@ -452,6 +478,9 @@ static void add_foreign_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, RelOptInfo *grouped_rel, GroupPathExtraData *extra); +static void add_foreign_ordered_paths(PlannerInfo *root, + RelOptInfo *input_rel, + RelOptInfo *ordered_rel); static void apply_server_options(PgFdwRelationInfo *fpinfo); static void apply_table_options(PgFdwRelationInfo *fpinfo); static void merge_fdw_options(PgFdwRelationInfo *fpinfo, @@ -637,7 +666,7 @@ postgresGetForeignRelSize(PlannerInfo *root, * values in fpinfo so we don't need to do it again to generate the * basic foreign path. */ - estimate_path_cost_size(root, baserel, NIL, NIL, + estimate_path_cost_size(root, baserel, NIL, NIL, NULL, &fpinfo->rows, &fpinfo->width, &fpinfo->startup_cost, &fpinfo->total_cost); @@ -668,7 +697,7 @@ postgresGetForeignRelSize(PlannerInfo *root, set_baserel_size_estimates(root, baserel); /* Fill in basically-bogus cost estimates for use later. */ - estimate_path_cost_size(root, baserel, NIL, NIL, + estimate_path_cost_size(root, baserel, NIL, NIL, NULL, &fpinfo->rows, &fpinfo->width, &fpinfo->startup_cost, &fpinfo->total_cost); } @@ -827,6 +856,7 @@ get_useful_pathkeys_for_relation(PlannerInfo *root, RelOptInfo *rel) * Pushing the query_pathkeys to the remote server is always worth * considering, because it might let us avoid a local sort. */ + fpinfo->qp_is_pushdown_safe = false; if (root->query_pathkeys) { bool query_pathkeys_ok = true; @@ -857,7 +887,10 @@ get_useful_pathkeys_for_relation(PlannerInfo *root, RelOptInfo *rel) } if (query_pathkeys_ok) + { useful_pathkeys_list = list_make1(list_copy(root->query_pathkeys)); + fpinfo->qp_is_pushdown_safe = true; + } } /* @@ -1102,7 +1135,7 @@ postgresGetForeignPaths(PlannerInfo *root, /* Get a cost estimate from the remote */ estimate_path_cost_size(root, baserel, - param_info->ppi_clauses, NIL, + param_info->ppi_clauses, NIL, NULL, &rows, &width, &startup_cost, &total_cost); @@ -1149,8 +1182,16 @@ postgresGetForeignPlan(PlannerInfo *root, List *fdw_recheck_quals = NIL; List *retrieved_attrs; StringInfoData sql; + bool has_final_sort = false; ListCell *lc; + /* + * Get FDW private data created by postgresGetForeignUpperPaths(), if any. + */ + if (best_path->fdw_private) + has_final_sort = intVal(list_nth(best_path->fdw_private, + FdwPathPrivateHasFinalSort)); + if (IS_SIMPLE_REL(foreignrel)) { /* @@ -1299,7 +1340,8 @@ postgresGetForeignPlan(PlannerInfo *root, initStringInfo(&sql); deparseSelectStmtForRel(&sql, root, foreignrel, fdw_scan_tlist, remote_exprs, best_path->path.pathkeys, - false, &retrieved_attrs, ¶ms_list); + has_final_sort, false, + &retrieved_attrs, ¶ms_list); /* Remember remote_exprs for possible use by postgresPlanDirectModify */ fpinfo->final_remote_exprs = remote_exprs; @@ -2482,6 +2524,8 @@ postgresExplainDirectModify(ForeignScanState *node, ExplainState *es) * * param_join_conds are the parameterization clauses with outer relations. * pathkeys specify the expected sort order if any for given path being costed. + * fpextra specifies additional post-scan/join-processing steps such as the + * final sort. * * The function returns the cost and size estimates in p_row, p_width, * p_startup_cost and p_total_cost variables. @@ -2491,6 +2535,7 @@ estimate_path_cost_size(PlannerInfo *root, RelOptInfo *foreignrel, List *param_join_conds, List *pathkeys, + PgFdwPathExtraData *fpextra, double *p_rows, int *p_width, Cost *p_startup_cost, Cost *p_total_cost) { @@ -2555,8 +2600,9 @@ estimate_path_cost_size(PlannerInfo *root, initStringInfo(&sql); appendStringInfoString(&sql, "EXPLAIN "); deparseSelectStmtForRel(&sql, root, foreignrel, fdw_scan_tlist, - remote_conds, pathkeys, false, - &retrieved_attrs, NULL); + remote_conds, pathkeys, + fpextra ? fpextra->has_final_sort : false, + false, &retrieved_attrs, NULL); /* Get the remote estimate */ conn = GetConnection(fpinfo->user, false); @@ -2624,9 +2670,9 @@ estimate_path_cost_size(PlannerInfo *root, /* * We will come here again and again with different set of pathkeys - * that caller wants to cost. We don't need to calculate the cost of - * bare scan each time. Instead, use the costs if we have cached them - * already. + * that caller wants to cost. We don't need to calculate the costs of + * the underlying scan, join, or grouping each time. Instead, use the + * costs if we have cached them already. */ if (fpinfo->rel_startup_cost >= 0 && fpinfo->rel_total_cost >= 0) { @@ -2844,23 +2890,51 @@ estimate_path_cost_size(PlannerInfo *root, */ if (pathkeys != NIL) { - startup_cost *= DEFAULT_FDW_SORT_MULTIPLIER; - run_cost *= DEFAULT_FDW_SORT_MULTIPLIER; + if (IS_UPPER_REL(foreignrel)) + { + Assert(fpinfo->stage == UPPERREL_GROUP_AGG); + adjust_foreign_grouping_path_cost(root, pathkeys, + retrieved_rows, width, + &startup_cost, &run_cost); + } + else + { + startup_cost *= DEFAULT_FDW_SORT_MULTIPLIER; + run_cost *= DEFAULT_FDW_SORT_MULTIPLIER; + } } total_cost = startup_cost + run_cost; } /* - * Cache the costs for scans without any pathkeys or parameterization - * before adding the costs for transferring data from the foreign server. - * These costs are useful for costing the join between this relation and - * another foreign relation or to calculate the costs of paths with - * pathkeys for this relation, when the costs can not be obtained from the - * foreign server. This function will be called at least once for every - * foreign relation without pathkeys and parameterization. + * If this includes the final sort step, the given target, which will be + * applied to the resulting path, might have different expressions from + * the foreignrel's reltarget (see make_sort_input_target()); adjust tlist + * eval costs. */ - if (pathkeys == NIL && param_join_conds == NIL) + if (fpextra && fpextra->target != foreignrel->reltarget) + { + QualCost oldcost = foreignrel->reltarget->cost; + QualCost newcost = fpextra->target->cost; + + startup_cost += newcost.startup - oldcost.startup; + total_cost += newcost.startup - oldcost.startup; + total_cost += (newcost.per_tuple - oldcost.per_tuple) * rows; + } + + /* + * Cache the costs for scans, joins, or groupings without any + * parameterization, pathkeys, or additional post-scan/join-processing + * steps, before adding the costs for transferring data from the foreign + * server. These costs are useful for costing remote joins involving this + * relation or costing other remote processing for this relation such as + * remote sort, when the costs can not be obtained from the foreign + * server. This function will be called at least once for every foreign + * relation without any parameterization, pathkeys, or additional + * post-scan/join-processing steps. + */ + if (pathkeys == NIL && param_join_conds == NIL && fpextra == NULL) { fpinfo->rel_startup_cost = startup_cost; fpinfo->rel_total_cost = total_cost; @@ -2935,6 +3009,57 @@ get_remote_estimate(const char *sql, PGconn *conn, PG_END_TRY(); } +/* + * Adjust the cost estimates of a foreign grouping path. + */ +static void +adjust_foreign_grouping_path_cost(PlannerInfo *root, + List *pathkeys, + double retrieved_rows, + double width, + Cost *p_startup_cost, + Cost *p_run_cost) +{ + /* + * If the GROUP BY clause isn't sort-able, the plan chosen by the remote + * side is unlikely to generate properly-sorted output, so it would need + * an explicit sort; adjust the given costs with cost_sort(). Likewise, + * if the GROUP BY clause is sort-able but isn't a superset of the given + * pathkeys, adjust the costs with that function. Otherwise, adjust the + * costs by applying the same heuristic as for the scan or join case. + */ + if (!grouping_is_sortable(root->parse->groupClause) || + !pathkeys_contained_in(pathkeys, root->group_pathkeys)) + { + Path sort_path; /* dummy for result of cost_sort */ + + cost_sort(&sort_path, + root, + pathkeys, + *p_startup_cost + *p_run_cost, + retrieved_rows, + width, + 0.0, + work_mem, + -1.0); + + *p_startup_cost = sort_path.startup_cost; + *p_run_cost = sort_path.total_cost - sort_path.startup_cost; + } + else + { + /* + * The default extra cost seems too large for foreign-grouping cases; + * add 1/4th of that default. + */ + double sort_multiplier = 1.0 + (DEFAULT_FDW_SORT_MULTIPLIER + - 1.0) * 0.25; + + *p_startup_cost *= sort_multiplier; + *p_run_cost *= sort_multiplier; + } +} + /* * Detect whether we want to process an EquivalenceClass member. * @@ -4934,7 +5059,7 @@ add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel, List *useful_pathkeys = lfirst(lc); Path *sorted_epq_path; - estimate_path_cost_size(root, rel, NIL, useful_pathkeys, + estimate_path_cost_size(root, rel, NIL, useful_pathkeys, NULL, &rows, &width, &startup_cost, &total_cost); /* @@ -5185,8 +5310,8 @@ postgresGetForeignJoinPaths(PlannerInfo *root, extra->sjinfo); /* Estimate costs for bare join relation */ - estimate_path_cost_size(root, joinrel, NIL, NIL, &rows, - &width, &startup_cost, &total_cost); + estimate_path_cost_size(root, joinrel, NIL, NIL, NULL, + &rows, &width, &startup_cost, &total_cost); /* Now update this information in the joinrel */ joinrel->rows = rows; joinrel->reltarget->width = width; @@ -5455,15 +5580,29 @@ postgresGetForeignUpperPaths(PlannerInfo *root, UpperRelationKind stage, return; /* Ignore stages we don't support; and skip any duplicate calls. */ - if (stage != UPPERREL_GROUP_AGG || output_rel->fdw_private) + if ((stage != UPPERREL_GROUP_AGG && + stage != UPPERREL_ORDERED) || + output_rel->fdw_private) return; fpinfo = (PgFdwRelationInfo *) palloc0(sizeof(PgFdwRelationInfo)); + fpinfo->stage = stage; fpinfo->pushdown_safe = false; output_rel->fdw_private = fpinfo; - add_foreign_grouping_paths(root, input_rel, output_rel, - (GroupPathExtraData *) extra); + switch (stage) + { + case UPPERREL_GROUP_AGG: + add_foreign_grouping_paths(root, input_rel, output_rel, + (GroupPathExtraData *) extra); + break; + case UPPERREL_ORDERED: + add_foreign_ordered_paths(root, input_rel, output_rel); + break; + default: + elog(ERROR, "unexpected upper relation: %d", (int) stage); + break; + } } /* @@ -5533,8 +5672,8 @@ add_foreign_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, cost_qual_eval(&fpinfo->local_conds_cost, fpinfo->local_conds, root); /* Estimate the cost of push down */ - estimate_path_cost_size(root, grouped_rel, NIL, NIL, &rows, - &width, &startup_cost, &total_cost); + estimate_path_cost_size(root, grouped_rel, NIL, NIL, NULL, + &rows, &width, &startup_cost, &total_cost); /* Now update this information in the fpinfo */ fpinfo->rows = rows; @@ -5542,6 +5681,8 @@ add_foreign_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, fpinfo->startup_cost = startup_cost; fpinfo->total_cost = total_cost; + grouped_rel->rows = fpinfo->rows; + /* Create and add foreign path to the grouping relation. */ grouppath = create_foreign_upper_path(root, grouped_rel, @@ -5557,6 +5698,137 @@ add_foreign_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, add_path(grouped_rel, (Path *) grouppath); } +/* + * add_foreign_ordered_paths + * Add foreign paths for performing the final sort remotely. + * + * Given input_rel contains the source-data Paths. The paths are added to the + * given ordered_rel. + */ +static void +add_foreign_ordered_paths(PlannerInfo *root, RelOptInfo *input_rel, + RelOptInfo *ordered_rel) +{ + Query *parse = root->parse; + PgFdwRelationInfo *ifpinfo = input_rel->fdw_private; + PgFdwRelationInfo *fpinfo = ordered_rel->fdw_private; + PgFdwPathExtraData *fpextra; + double rows; + int width; + Cost startup_cost; + Cost total_cost; + List *fdw_private; + ForeignPath *ordered_path; + ListCell *lc; + + /* Shouldn't get here unless the query has ORDER BY */ + Assert(parse->sortClause); + + /* We don't support cases where there are any SRFs in the targetlist. */ + if (parse->hasTargetSRFs) + return; + + /* Save the input_rel as outerrel in fpinfo */ + fpinfo->outerrel = input_rel; + + /* + * Copy foreign table, foreign server, user mapping, FDW options etc. + * details from the input relation's fpinfo. + */ + fpinfo->table = ifpinfo->table; + fpinfo->server = ifpinfo->server; + fpinfo->user = ifpinfo->user; + merge_fdw_options(fpinfo, ifpinfo, NULL); + + /* + * If the input_rel is a base or join relation, we would already have + * considered pushing down the final sort to the remote server when + * creating pre-sorted foreign paths for that relation, because the + * query_pathkeys is set to the root->sort_pathkeys in that case (see + * standard_qp_callback()). + */ + if (input_rel->reloptkind == RELOPT_BASEREL || + input_rel->reloptkind == RELOPT_JOINREL) + { + Assert(root->query_pathkeys == root->sort_pathkeys); + + fpinfo->pushdown_safe = ifpinfo->qp_is_pushdown_safe; + + return; + } + + /* The input_rel should be a grouping relation */ + Assert(input_rel->reloptkind == RELOPT_UPPER_REL); + Assert(ifpinfo->stage == UPPERREL_GROUP_AGG); + + /* + * We try to create a path below by extending a simple foreign path for + * the underlying grouping relation to perform the final sort remotely, + * which is stored into the fdw_private list of the resulting path. + */ + + /* Assess if it is safe to push down the final sort */ + foreach(lc, root->sort_pathkeys) + { + PathKey *pathkey = (PathKey *) lfirst(lc); + EquivalenceClass *pathkey_ec = pathkey->pk_eclass; + Expr *sort_expr; + + /* + * is_foreign_expr would detect volatile expressions as well, but + * checking ec_has_volatile here saves some cycles. + */ + if (pathkey_ec->ec_has_volatile) + return; + + /* Get the sort expression for the pathkey_ec */ + sort_expr = find_em_expr_for_input_target(root, + pathkey_ec, + input_rel->reltarget); + + /* If it's unsafe to remote, we cannot push down the final sort */ + if (!is_foreign_expr(root, input_rel, sort_expr)) + return; + } + + /* Safe to push down */ + fpinfo->pushdown_safe = true; + + /* Initialize the selectivity and cost of local_conds */ + fpinfo->local_conds_sel = 1.0; + fpinfo->local_conds_cost.startup = 0.0; + fpinfo->local_conds_cost.per_tuple = 0.0; + + /* Construct PgFdwPathExtraData */ + fpextra = (PgFdwPathExtraData *) palloc0(sizeof(PgFdwPathExtraData)); + fpextra->target = root->upper_targets[UPPERREL_ORDERED]; + fpextra->has_final_sort = true; + + /* Estimate the costs of performing the final sort remotely */ + estimate_path_cost_size(root, input_rel, NIL, root->sort_pathkeys, fpextra, + &rows, &width, &startup_cost, &total_cost); + + /* + * Build the fdw_private list that will be used by postgresGetForeignPlan. + * Items in the list must match order in enum FdwPathPrivateIndex. + */ + fdw_private = list_make1(makeInteger(true)); + + /* Create foreign ordering ForeignPath */ + ordered_path = create_foreign_upper_path(root, + input_rel, + root->upper_targets[UPPERREL_ORDERED], + rows, + startup_cost, + total_cost, + root->sort_pathkeys, + NULL, /* no extra plan */ + fdw_private); + + /* and add it to the ordered_rel */ + add_path(ordered_rel, (Path *) ordered_path); +} + /* * Create a tuple from the specified row of the PGresult. * @@ -5807,3 +6079,65 @@ find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel) /* We didn't find any suitable equivalence class expression */ return NULL; } + +/* + * Find an equivalence class member expression to be computed as a sort column + * in the given target. + */ +Expr * +find_em_expr_for_input_target(PlannerInfo *root, + EquivalenceClass *ec, + PathTarget *target) +{ + ListCell *lc1; + int i; + + i = 0; + foreach(lc1, target->exprs) + { + Expr *expr = (Expr *) lfirst(lc1); + Index sgref = get_pathtarget_sortgroupref(target, i); + ListCell *lc2; + + /* Ignore non-sort expressions */ + if (sgref == 0 || + get_sortgroupref_clause_noerr(sgref, + root->parse->sortClause) == NULL) + { + i++; + continue; + } + + /* We ignore binary-compatible relabeling on both ends */ + while (expr && IsA(expr, RelabelType)) + expr = ((RelabelType *) expr)->arg; + + /* Locate an EquivalenceClass member matching this expr, if any */ + foreach(lc2, ec->ec_members) + { + EquivalenceMember *em = (EquivalenceMember *) lfirst(lc2); + Expr *em_expr; + + /* Don't match constants */ + if (em->em_is_const) + continue; + + /* Ignore child members */ + if (em->em_is_child) + continue; + + /* Match if same expression (after stripping relabel) */ + em_expr = em->em_expr; + while (em_expr && IsA(em_expr, RelabelType)) + em_expr = ((RelabelType *) em_expr)->arg; + + if (equal(em_expr, expr)) + return em->em_expr; + } + + i++; + } + + elog(ERROR, "could not find pathkey item to sort"); + return NULL; /* keep compiler quiet */ +} diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h index 3f50103285..e9cbbe6234 100644 --- a/contrib/postgres_fdw/postgres_fdw.h +++ b/contrib/postgres_fdw/postgres_fdw.h @@ -49,6 +49,9 @@ typedef struct PgFdwRelationInfo /* Bitmap of attr numbers we need to fetch from the remote server. */ Bitmapset *attrs_used; + /* True means that the query_pathkeys is safe to push down */ + bool qp_is_pushdown_safe; + /* Cost and selectivity of local_conds. */ QualCost local_conds_cost; Selectivity local_conds_sel; @@ -92,6 +95,9 @@ typedef struct PgFdwRelationInfo /* joinclauses contains only JOIN/ON conditions for an outer join */ List *joinclauses; /* List of RestrictInfo */ + /* Upper relation information */ + UpperRelationKind stage; + /* Grouping information */ List *grouped_tlist; @@ -175,10 +181,14 @@ extern void deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs); extern void deparseStringLiteral(StringInfo buf, const char *val); extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel); +extern Expr *find_em_expr_for_input_target(PlannerInfo *root, + EquivalenceClass *ec, + PathTarget *target); extern List *build_tlist_to_deparse(RelOptInfo *foreignrel); extern void deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel, List *tlist, - List *remote_conds, List *pathkeys, bool is_subquery, + List *remote_conds, List *pathkeys, + bool has_final_sort, bool is_subquery, List **retrieved_attrs, List **params_list); extern const char *get_jointype_name(JoinType jointype); -- 2.19.2 --------------080806040701020705050304 Content-Type: text/x-patch; name="v7-0002-Refactor-create_limit_path-to-share-cost-adjustment-.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="v7-0002-Refactor-create_limit_path-to-share-cost-adjustment-"; filename*1=".patch" ^ permalink raw reply [nested|flat] 3+ messages in thread
* [PATCH v21 6/8] Row pattern recognition patch (docs). @ 2024-08-26 04:32 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Tatsuo Ishii @ 2024-08-26 04:32 UTC (permalink / raw) --- doc/src/sgml/advanced.sgml | 82 ++++++++++++++++++++++++++++++++++++ doc/src/sgml/func.sgml | 54 ++++++++++++++++++++++++ doc/src/sgml/ref/select.sgml | 38 ++++++++++++++++- 3 files changed, 172 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 755c9f1485..b0b1d1c51e 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -537,6 +537,88 @@ WHERE pos < 3; <literal>rank</literal> less than 3. </para> + <para> + Row pattern common syntax can be used to perform row pattern recognition + in a query. The row pattern common syntax includes two sub + clauses: <literal>DEFINE</literal> + and <literal>PATTERN</literal>. <literal>DEFINE</literal> defines + definition variables along with an expression. The expression must be a + logical expression, which means it must + return <literal>TRUE</literal>, <literal>FALSE</literal> + or <literal>NULL</literal>. The expression may comprise column references + and functions. Window functions, aggregate functions and subqueries are + not allowed. An example of <literal>DEFINE</literal> is as follows. + +<programlisting> +DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +</programlisting> + + Note that <function>PREV</function> returns the price column in the + previous row if it's called in a context of row pattern recognition. Thus in + the second line the definition variable "UP" is <literal>TRUE</literal> + when the price column in the current row is greater than the price column + in the previous row. Likewise, "DOWN" is <literal>TRUE</literal> when when + the price column in the current row is lower than the price column in the + previous row. + </para> + <para> + Once <literal>DEFINE</literal> exists, <literal>PATTERN</literal> can be + used. <literal>PATTERN</literal> defines a sequence of rows that satisfies + certain conditions. For example following <literal>PATTERN</literal> + defines that a row starts with the condition "LOWPRICE", then one or more + rows satisfy "UP" and finally one or more rows satisfy "DOWN". Note that + "+" means one or more matches. Also you can use "*", which means zero or + more matches. If a sequence of rows which satisfies the PATTERN is found, + in the starting row of the sequence of rows all window functions and + aggregates are shown in the target list. Note that aggregations only look + into the matched rows, rather than whole frame. On the second or + subsequent rows all window functions are NULL. Aggregates are NULL or 0 + (count case) depending on its aggregation definition. For rows that do not + match on the PATTERN, all window functions and aggregates are shown AS + NULL too, except count showing 0. This is because the rows do not match, + thus they are in an empty frame. Example of a <literal>SELECT</literal> + using the <literal>DEFINE</literal> and <literal>PATTERN</literal> clause + is as follows. + +<programlisting> +SELECT company, tdate, price, + first_value(price) OVER w, + max(price) OVER w, + count(price) OVER w +FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); +</programlisting> +<screen> + company | tdate | price | first_value | max | count +----------+------------+-------+-------------+-----+------- + company1 | 2023-07-01 | 100 | 100 | 200 | 4 + company1 | 2023-07-02 | 200 | | | 0 + company1 | 2023-07-03 | 150 | | | 0 + company1 | 2023-07-04 | 140 | | | 0 + company1 | 2023-07-05 | 150 | | | 0 + company1 | 2023-07-06 | 90 | 90 | 130 | 4 + company1 | 2023-07-07 | 110 | | | 0 + company1 | 2023-07-08 | 130 | | | 0 + company1 | 2023-07-09 | 120 | | | 0 + company1 | 2023-07-10 | 130 | | | 0 +(10 rows) +</screen> + </para> + <para> When a query involves multiple window functions, it is possible to write out each one with a separate <literal>OVER</literal> clause, but this is diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 461fc3f437..02ad2b0195 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -23258,6 +23258,7 @@ SELECT count(*) FROM sometable; returns <literal>NULL</literal> if there is no such row. </para></entry> </row> + </tbody> </tgroup> </table> @@ -23297,6 +23298,59 @@ SELECT count(*) FROM sometable; Other frame specifications can be used to obtain other effects. </para> + <para> + Row pattern recognition navigation functions are listed in + <xref linkend="functions-rpr-navigation-table"/>. These functions + can be used to describe DEFINE clause of Row pattern recognition. + </para> + + <table id="functions-rpr-navigation-table"> + <title>Row Pattern Navigation Functions</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="func_table_entry"><para role="func_signature"> + Function + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>prev</primary> + </indexterm> + <function>prev</function> ( <parameter>value</parameter> <type>anyelement</type> ) + <returnvalue>anyelement</returnvalue> + </para> + <para> + Returns the column value at the previous row; + returns NULL if there is no previous row in the window frame. + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>next</primary> + </indexterm> + <function>next</function> ( <parameter>value</parameter> <type>anyelement</type> ) + <returnvalue>anyelement</returnvalue> + </para> + <para> + Returns the column value at the next row; + returns NULL if there is no next row in the window frame. + </para></entry> + </row> + + </tbody> + </tgroup> + </table> + <note> <para> The SQL standard defines a <literal>RESPECT NULLS</literal> or diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index d7089eac0b..7e1c9989ba 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -969,8 +969,8 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl The <replaceable class="parameter">frame_clause</replaceable> can be one of <synopsis> -{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] -{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] +{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax] +{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax] </synopsis> where <replaceable>frame_start</replaceable> @@ -1077,6 +1077,40 @@ EXCLUDE NO OTHERS a given peer group will be in the frame or excluded from it. </para> + <para> + The + optional <replaceable class="parameter">row_pattern_common_syntax</replaceable> + defines the <firstterm>row pattern recognition condition</firstterm> for + this + window. <replaceable class="parameter">row_pattern_common_syntax</replaceable> + includes following subclauses. <literal>AFTER MATCH SKIP PAST LAST + ROW</literal> or <literal>AFTER MATCH SKIP TO NEXT ROW</literal> controls + how to proceed to next row position after a match + found. With <literal>AFTER MATCH SKIP PAST LAST ROW</literal> (the + default) next row position is next to the last row of previous match. On + the other hand, with <literal>AFTER MATCH SKIP TO NEXT ROW</literal> next + row position is always next to the last row of previous + match. <literal>DEFINE</literal> defines definition variables along with a + boolean expression. <literal>PATTERN</literal> defines a sequence of rows + that satisfies certain conditions using variables defined + in <literal>DEFINE</literal> clause. If the variable is not defined in + the <literal>DEFINE</literal> clause, it is implicitly assumed + following is defined in the <literal>DEFINE</literal> clause. + +<synopsis> +<literal>variable_name</literal> AS TRUE +</synopsis> + + Note that the maximu number of variables defined + in <literal>DEFINE</literal> clause is 26. + +<synopsis> +[ AFTER MATCH SKIP PAST LAST ROW | AFTER MATCH SKIP TO NEXT ROW ] +PATTERN <replaceable class="parameter">pattern_variable_name</replaceable>[+] [, ...] +DEFINE <replaceable class="parameter">definition_varible_name</replaceable> AS <replaceable class="parameter">expression</replaceable> [, ...] +</synopsis> + </para> + <para> The purpose of a <literal>WINDOW</literal> clause is to specify the behavior of <firstterm>window functions</firstterm> appearing in the query's -- 2.25.1 ----Next_Part(Mon_Aug_26_13_39_47_2024_878)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v21-0007-Row-pattern-recognition-patch-tests.patch" ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Alias of VALUES RTE in explain plan @ 2024-10-29 13:38 Ashutosh Bapat <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Ashutosh Bapat @ 2024-10-29 13:38 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Andrei Lepikhov <[email protected]>; Yasir <[email protected]>; PostgreSQL Hackers <[email protected]> On Mon, Oct 28, 2024 at 8:46 PM Tom Lane <[email protected]> wrote: > > Ashutosh Bapat <[email protected]> writes: > > The patch looks good to me, except the name of the new member. > > > CommonTableExpr *p_parent_cte; /* this query's containing CTE */ > > + Alias *p_parent_alias; /* parent's alias for this query */ > > > the two "parent"s here mean different things and that might lead one > > to assume that the p_parent_alias refers to alias of CTE. The comment > > adds to the confusion since it mentions parent. How about renaming it > > as p_outer_alias? or something which indicates alias of the outer > > query? > > Hmm, I figured the two "parent" references do mean the same thing, > ie the immediately surrounding syntactic construct. While I won't > fight hard about it, I don't see an advantage in naming the new > field differently. We could make the comment be > > /* outer level's alias for this query */ WFM. -- Best Wishes, Ashutosh Bapat ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-10-29 13:38 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-03-20 10:19 [PATCH 1/3] postgres_fdw: Perform UPPERREL_ORDERED step remotely Etsuro Fujita <[email protected]> 2024-08-26 04:32 [PATCH v21 6/8] Row pattern recognition patch (docs). Tatsuo Ishii <[email protected]> 2024-10-29 13:38 Re: Alias of VALUES RTE in explain plan Ashutosh Bapat <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox