This is an automated email from the ASF dual-hosted git repository. avamingli pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 5ed2e6a77c660ad649833018c1df2965c8d133ee Author: Zhang Mingli <[email protected]> AuthorDate: Mon Apr 29 12:27:25 2024 +0800 Optimize MPP FDW LIMIT/OFFSET push down when there is NULL/0. (#17246) When there are NULL or zero values in OFFSET/LIMIT clause, we do NOT need to fetch more rows and expression NULL plus others is pointless. Optimize (N > 0) LIMIT 0 OFFSET N to LIMIT 0 LIMIT 0 OFFSET NULL to LIMIT 0 LIMIT N OFFSET NULL to LIMIT N LIMIT N OFFSET 0 to LIMIT N Example before this fix: EXPLAIN (VERBOSE, COSTS OFF) SELECT c1, c2 FROM mpp_ft2 order by c1 limit 0 offset 998; QUERY PLAN -------------- Limit Output: c1, c2 -> Gather Motion 2:1 (slice1; segments: 2) Output: c1, c2 Merge Key: c1 -> Foreign Scan on public.mpp_ft2 Output: c1, c2 Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" ORDER BY c1 ASC NULLS LAST LIMIT (998::bigint + 0::bigint) We will have to fetch 998 rows from remote, but as we have limit 0, that's pointless. With this fix: EXPLAIN (VERBOSE, COSTS OFF) SELECT c1, c2 FROM mpp_ft2 order by c1 limit 0 offset 998; QUERY PLAN -------------- Limit Output: c1, c2 -> Gather Motion 2:1 (slice1; segments: 2) Output: c1, c2 Merge Key: c1 -> Foreign Scan on public.mpp_ft2 Output: c1, c2 Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" ORDER BY c1 ASC NULLS LAST LIMIT 0::bigint --- contrib/postgres_fdw/Makefile | 10 +++ contrib/postgres_fdw/deparse.c | 21 ++++- .../expected/mpp_gp2pg_postgres_fdw.out | 99 ++++++++++++++++++++++ .../postgres_fdw/sql/mpp_gp2pg_postgres_fdw.sql | 17 ++++ 4 files changed, 145 insertions(+), 2 deletions(-) diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile index 6c07120092..8eb967ca87 100644 --- a/contrib/postgres_fdw/Makefile +++ b/contrib/postgres_fdw/Makefile @@ -45,3 +45,13 @@ clean: clean-symlinks clean-symlinks: rm -f link-canary.c + +# For postgres_fdw test +export PG_PORT=5432 +installcheck: install prep_postgres +clean: clean_postgres +prep_postgres: + ./postgres_setup.bash +clean_postgres: + ./postgres_clean.bash +.PHONY: prep_postgres clean_postgres diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 19d8e9cc83..51e3c5cfb4 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -3449,14 +3449,31 @@ appendLimitClause(deparse_expr_cxt *context) * This may reduce the number of tuples that we need to fetch from remote servers. */ Node *precount = copyObject(root->parse->limitCount); + Node *offset = copyObject(root->parse->limitOffset); + bool offset_needed = ((precount != NULL) && (offset != NULL)); /* * If we've specified both OFFSET and LIMIT clause, * it's enough to fetch tuples from 0 to limitCount + limitOffset from remote servers. + * optimize (N > 0): + * LIMIT 0 OFFSET N to LIMIT 0 + * LIMIT 0 OFFSET NULL to LIMIT 0 + * LIMIT N OFFSET NULL to LIMIT N + * LIMIT N OFFSET 0 to LIMIT N */ + if (offset_needed && + IsA(precount, Const) && + (((Const *) precount)->constisnull || ((Const *) precount)->constvalue == 0)) + offset_needed = false; + + if (offset_needed && + IsA(offset, Const) && + (((Const *) offset)->constisnull || ((Const *) offset)->constvalue == 0)) + offset_needed = false; + if (precount) { - if (root->parse->limitOffset) + if (offset_needed) { ParseState *pstate = make_parsestate(NULL); /* @@ -3465,7 +3482,7 @@ appendLimitClause(deparse_expr_cxt *context) */ precount = (Node *) make_op(pstate, list_make2(makeString("pg_catalog"), makeString(pstrdup("+"))), - copyObject(root->parse->limitOffset), + offset, precount, NULL, -1); diff --git a/contrib/postgres_fdw/expected/mpp_gp2pg_postgres_fdw.out b/contrib/postgres_fdw/expected/mpp_gp2pg_postgres_fdw.out index 61496856b2..097ea641e0 100644 --- a/contrib/postgres_fdw/expected/mpp_gp2pg_postgres_fdw.out +++ b/contrib/postgres_fdw/expected/mpp_gp2pg_postgres_fdw.out @@ -981,6 +981,105 @@ SELECT c1, c2 FROM mpp_ft2 order by c1 offset 998; 1000 | 0 (2 rows) +-- test LIMIT 0, OFFSET null/0 +ALTER FOREIGN TABLE mpp_ft2 OPTIONS(set use_remote_estimate 'true'); +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1, c2 FROM mpp_ft2 order by c1 limit null offset 998; + QUERY PLAN +------------------------------------------------------------------------------------------------------------ + Limit + Output: c1, c2 + -> Gather Motion 2:1 (slice1; segments: 2) + Output: c1, c2 + Merge Key: c1 + -> Foreign Scan on public.mpp_ft2 + Output: c1, c2 + Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" ORDER BY c1 ASC NULLS LAST LIMIT NULL::bigint + Optimizer: Postgres-based planner + Settings: gp_enable_minmax_optimization = 'off' +(10 rows) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1, c2 FROM mpp_ft2 order by c1 limit all offset 998; + QUERY PLAN +------------------------------------------------------------------------------------------------------------ + Limit + Output: c1, c2 + -> Gather Motion 2:1 (slice1; segments: 2) + Output: c1, c2 + Merge Key: c1 + -> Foreign Scan on public.mpp_ft2 + Output: c1, c2 + Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" ORDER BY c1 ASC NULLS LAST LIMIT NULL::bigint + Optimizer: Postgres-based planner + Settings: gp_enable_minmax_optimization = 'off' +(10 rows) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1, c2 FROM mpp_ft2 order by c1 limit 0 offset 998; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- + Limit + Output: c1, c2 + -> Gather Motion 2:1 (slice1; segments: 2) + Output: c1, c2 + Merge Key: c1 + -> Foreign Scan on public.mpp_ft2 + Output: c1, c2 + Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" ORDER BY c1 ASC NULLS LAST LIMIT 0::bigint + Optimizer: Postgres-based planner + Settings: gp_enable_minmax_optimization = 'off' +(10 rows) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1, c2 FROM mpp_ft2 order by c1 limit 0 offset null; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------- + Limit + Output: c1, c2 + -> Gather Motion 2:1 (slice1; segments: 2) + Output: c1, c2 + Merge Key: c1 + -> Foreign Scan on public.mpp_ft2 + Output: c1, c2 + Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" ORDER BY c1 ASC NULLS LAST LIMIT 0::bigint + Optimizer: Postgres-based planner + Settings: gp_enable_minmax_optimization = 'off' +(10 rows) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1, c2 FROM mpp_ft2 order by c1 limit 3 offset null; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------- + Limit + Output: c1, c2 + -> Gather Motion 2:1 (slice1; segments: 2) + Output: c1, c2 + Merge Key: c1 + -> Foreign Scan on public.mpp_ft2 + Output: c1, c2 + Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" ORDER BY c1 ASC NULLS LAST LIMIT 3::bigint + Optimizer: Postgres-based planner + Settings: gp_enable_minmax_optimization = 'off' +(10 rows) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1, c2 FROM mpp_ft2 order by c1 limit 3 offset 0; + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------- + Limit + Output: c1, c2 + -> Gather Motion 2:1 (slice1; segments: 2) + Output: c1, c2 + Merge Key: c1 + -> Foreign Scan on public.mpp_ft2 + Output: c1, c2 + Remote SQL: SELECT c1, c2 FROM "MPP_S 1"."T 2" ORDER BY c1 ASC NULLS LAST LIMIT 3::bigint + Optimizer: Postgres-based planner + Settings: gp_enable_minmax_optimization = 'off' +(10 rows) + +ALTER FOREIGN TABLE mpp_ft2 OPTIONS(set use_remote_estimate 'false'); -- Query with aggregates and limit clause together is NOT pushed down. -- Because it's unsafe to do partial aggregate and limit in multiple remote servers. EXPLAIN (VERBOSE, COSTS OFF) diff --git a/contrib/postgres_fdw/sql/mpp_gp2pg_postgres_fdw.sql b/contrib/postgres_fdw/sql/mpp_gp2pg_postgres_fdw.sql index 2ebb297c7d..cc9bd2fe2f 100644 --- a/contrib/postgres_fdw/sql/mpp_gp2pg_postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/mpp_gp2pg_postgres_fdw.sql @@ -233,6 +233,23 @@ SELECT c1, c2 FROM mpp_ft2 order by c1 offset 2 limit 3; EXPLAIN VERBOSE SELECT c1, c2 FROM mpp_ft2 order by c1 offset 998; SELECT c1, c2 FROM mpp_ft2 order by c1 offset 998; + +-- test LIMIT 0, OFFSET null/0 +ALTER FOREIGN TABLE mpp_ft2 OPTIONS(set use_remote_estimate 'true'); +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1, c2 FROM mpp_ft2 order by c1 limit null offset 998; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1, c2 FROM mpp_ft2 order by c1 limit all offset 998; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1, c2 FROM mpp_ft2 order by c1 limit 0 offset 998; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1, c2 FROM mpp_ft2 order by c1 limit 0 offset null; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1, c2 FROM mpp_ft2 order by c1 limit 3 offset null; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1, c2 FROM mpp_ft2 order by c1 limit 3 offset 0; +ALTER FOREIGN TABLE mpp_ft2 OPTIONS(set use_remote_estimate 'false'); + -- Query with aggregates and limit clause together is NOT pushed down. -- Because it's unsafe to do partial aggregate and limit in multiple remote servers. EXPLAIN (VERBOSE, COSTS OFF) --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
