diff options
Diffstat (limited to 'contrib/postgres_fdw/expected/postgres_fdw.out')
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 132 |
1 files changed, 132 insertions, 0 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 6dcbcc598d6..fa0d1db5fbc 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -7851,3 +7851,135 @@ SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE (14 rows) RESET enable_partitionwise_join; +-- =================================================================== +-- test partitionwise aggregates +-- =================================================================== +CREATE TABLE pagg_tab (a int, b int, c text) PARTITION BY RANGE(a); +CREATE TABLE pagg_tab_p1 (LIKE pagg_tab); +CREATE TABLE pagg_tab_p2 (LIKE pagg_tab); +CREATE TABLE pagg_tab_p3 (LIKE pagg_tab); +INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 10; +INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10; +INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20; +-- Create foreign partitions +CREATE FOREIGN TABLE fpagg_tab_p1 PARTITION OF pagg_tab FOR VALUES FROM (0) TO (10) SERVER loopback OPTIONS (table_name 'pagg_tab_p1'); +CREATE FOREIGN TABLE fpagg_tab_p2 PARTITION OF pagg_tab FOR VALUES FROM (10) TO (20) SERVER loopback OPTIONS (table_name 'pagg_tab_p2');; +CREATE FOREIGN TABLE fpagg_tab_p3 PARTITION OF pagg_tab FOR VALUES FROM (20) TO (30) SERVER loopback OPTIONS (table_name 'pagg_tab_p3');; +ANALYZE pagg_tab; +ANALYZE fpagg_tab_p1; +ANALYZE fpagg_tab_p2; +ANALYZE fpagg_tab_p3; +-- When GROUP BY clause matches with PARTITION KEY. +-- Plan with partitionwise aggregates is disabled +SET enable_partitionwise_aggregate TO false; +EXPLAIN (COSTS OFF) +SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; + QUERY PLAN +------------------------------------------------------- + Sort + Sort Key: fpagg_tab_p1.a + -> HashAggregate + Group Key: fpagg_tab_p1.a + Filter: (avg(fpagg_tab_p1.b) < '22'::numeric) + -> Append + -> Foreign Scan on fpagg_tab_p1 + -> Foreign Scan on fpagg_tab_p2 + -> Foreign Scan on fpagg_tab_p3 +(9 rows) + +-- Plan with partitionwise aggregates is enabled +SET enable_partitionwise_aggregate TO true; +EXPLAIN (COSTS OFF) +SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; + QUERY PLAN +---------------------------------------------------------------------- + Sort + Sort Key: fpagg_tab_p1.a + -> Append + -> Foreign Scan + Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab) + -> Foreign Scan + Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab) + -> Foreign Scan + Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab) +(9 rows) + +SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; + a | sum | min | count +----+------+-----+------- + 0 | 2000 | 0 | 100 + 1 | 2100 | 1 | 100 + 10 | 2000 | 0 | 100 + 11 | 2100 | 1 | 100 + 20 | 2000 | 0 | 100 + 21 | 2100 | 1 | 100 +(6 rows) + +-- Check with whole-row reference +-- Should have all the columns in the target list for the given relation +EXPLAIN (VERBOSE, COSTS OFF) +SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1; + QUERY PLAN +------------------------------------------------------------------------ + Sort + Output: t1.a, (count(((t1.*)::pagg_tab))) + Sort Key: t1.a + -> Append + -> HashAggregate + Output: t1.a, count(((t1.*)::pagg_tab)) + Group Key: t1.a + Filter: (avg(t1.b) < '22'::numeric) + -> Foreign Scan on public.fpagg_tab_p1 t1 + Output: t1.a, t1.*, t1.b + Remote SQL: SELECT a, b, c FROM public.pagg_tab_p1 + -> HashAggregate + Output: t1_1.a, count(((t1_1.*)::pagg_tab)) + Group Key: t1_1.a + Filter: (avg(t1_1.b) < '22'::numeric) + -> Foreign Scan on public.fpagg_tab_p2 t1_1 + Output: t1_1.a, t1_1.*, t1_1.b + Remote SQL: SELECT a, b, c FROM public.pagg_tab_p2 + -> HashAggregate + Output: t1_2.a, count(((t1_2.*)::pagg_tab)) + Group Key: t1_2.a + Filter: (avg(t1_2.b) < '22'::numeric) + -> Foreign Scan on public.fpagg_tab_p3 t1_2 + Output: t1_2.a, t1_2.*, t1_2.b + Remote SQL: SELECT a, b, c FROM public.pagg_tab_p3 +(25 rows) + +SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1; + a | count +----+------- + 0 | 100 + 1 | 100 + 10 | 100 + 11 | 100 + 20 | 100 + 21 | 100 +(6 rows) + +-- When GROUP BY clause does not match with PARTITION KEY. +EXPLAIN (COSTS OFF) +SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1; + QUERY PLAN +------------------------------------------------------ + Sort + Sort Key: fpagg_tab_p1.b + -> Finalize HashAggregate + Group Key: fpagg_tab_p1.b + Filter: (sum(fpagg_tab_p1.a) < 700) + -> Append + -> Partial HashAggregate + Group Key: fpagg_tab_p1.b + -> Foreign Scan on fpagg_tab_p1 + -> Partial HashAggregate + Group Key: fpagg_tab_p2.b + -> Foreign Scan on fpagg_tab_p2 + -> Partial HashAggregate + Group Key: fpagg_tab_p3.b + -> Foreign Scan on fpagg_tab_p3 +(15 rows) + +-- Clean-up +RESET enable_partitionwise_aggregate; |