"David E. Wheeler" <[email protected]> writes:
> On Jan 5, 2026, at 19:41, Tom Lane <[email protected]> wrote:
>> Yeah, it looks a little
>> odd to ship the query in this form rather than as a MIN()/MAX(),
>> but I'm unconvinced that it's harmful per se.
> It would be less harmful if the LIMIT pushed down; otherwise it’s pulling the
> entire table back into Postgres, no?
Yeah, there might be some gold to be mined there. IIRC we already
have some ability for LIMIT to tell an immediately-below Sort that
only a limited number of rows will be retrieved. Could be useful
if ForeignScan could be in on that.
>> It would be good to look under the hood a little more and understand
>> why the MinMaxAgg path is winning, if it's not actually fast.
> Where does one start?
I made the attached test case (based on postgres_fdw.sql, but with
a few more rows in the table). The idea is to check the behavior
for both an indexed column (c1) and an unindexed one (c2). To
eliminate cacheing/startup effects, I ran each test case three times
but considered only the lowest execution time of the three (often
but not always the last run).
The first attached results are from current HEAD; the second are
after applying your patch. What I find interesting is that
with use_remote_estimate enabled, it always goes for shipping the
MIN as-is. Your patch changes the behavior with use_remote_estimate
disabled, and we can see that the reason is that that mode estimates
the query cost at 100.24 with the minmax optimization and 146.78 when
shipping the aggregate. I've not dug into where we are getting
those numbers without use_remote_estimate, but perhaps that could
use refinement. On the other hand, there is not that much difference
between the actual runtimes, so perhaps it doesn't matter for
postgres_fdw.
I can't speak to your results with ClickHouse, since I have no idea
how the cost estimates are derived for that.
regards, tom lane
CREATE EXTENSION postgres_fdw;
SELECT current_database() AS current_database,
current_setting('port') AS current_port
\gset
CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname :'current_database', port :'current_port');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 3" (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
CONSTRAINT t3_pkey PRIMARY KEY (c1)
);
INSERT INTO "S 1"."T 3"
SELECT id,
id + 1,
'AAA' || to_char(id, 'FM000')
FROM generate_series(1, 10000) id;
ANALYZE "S 1"."T 3";
CREATE FOREIGN TABLE ft4 (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3');
explain (analyze, verbose) select min(c1) from "S 1"."T 3";
explain (analyze, verbose) select min(c1) from "S 1"."T 3";
explain (analyze, verbose) select min(c1) from "S 1"."T 3";
explain (analyze, verbose) select min(c2) from "S 1"."T 3";
explain (analyze, verbose) select min(c2) from "S 1"."T 3";
explain (analyze, verbose) select min(c2) from "S 1"."T 3";
ALTER FOREIGN TABLE ft4 OPTIONS (use_remote_estimate 'false');
explain (analyze, verbose) select min(c1) from ft4;
explain (analyze, verbose) select min(c1) from ft4;
explain (analyze, verbose) select min(c1) from ft4;
explain (analyze, verbose) select min(c2) from ft4;
explain (analyze, verbose) select min(c2) from ft4;
explain (analyze, verbose) select min(c2) from ft4;
ALTER FOREIGN TABLE ft4 OPTIONS (SET use_remote_estimate 'true');
explain (analyze, verbose) select min(c1) from ft4;
explain (analyze, verbose) select min(c1) from ft4;
explain (analyze, verbose) select min(c1) from ft4;
explain (analyze, verbose) select min(c2) from ft4;
explain (analyze, verbose) select min(c2) from ft4;
explain (analyze, verbose) select min(c2) from ft4;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.32..0.33 rows=1 width=4) (actual time=0.012..0.012 rows=1.00
loops=1)
Output: (InitPlan minmax_1).col1
Replaces: MinMaxAggregate
Buffers: shared hit=3
InitPlan minmax_1
-> Limit (cost=0.29..0.32 rows=1 width=4) (actual time=0.011..0.011
rows=1.00 loops=1)
Output: "T 3".c1
Buffers: shared hit=3
-> Index Only Scan using t3_pkey on "S 1"."T 3" (cost=0.29..328.29
rows=10000 width=4) (actual time=0.011..0.011 rows=1.00 loops=1)
Output: "T 3".c1
Heap Fetches: 1
Index Searches: 1
Buffers: shared hit=3
Planning Time: 0.027 ms
Execution Time: 0.019 ms
(15 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=180.00..180.01 rows=1 width=4) (actual time=1.057..1.058
rows=1.00 loops=1)
Output: min(c2)
Buffers: shared hit=55
-> Seq Scan on "S 1"."T 3" (cost=0.00..155.00 rows=10000 width=4) (actual
time=0.004..0.414 rows=10000.00 loops=1)
Output: c1, c2, c3
Buffers: shared hit=55
Planning Time: 0.026 ms
Execution Time: 1.065 ms
(8 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Result (cost=100.23..100.24 rows=1 width=4) (actual time=0.145..0.145
rows=1.00 loops=1)
Output: (InitPlan minmax_1).col1
Replaces: MinMaxAggregate
InitPlan minmax_1
-> Limit (cost=100.00..100.23 rows=1 width=4) (actual time=0.144..0.144
rows=1.00 loops=1)
Output: ft4.c1
-> Foreign Scan on public.ft4 (cost=100.00..761.35 rows=2925
width=4) (actual time=0.144..0.144 rows=1.00 loops=1)
Output: ft4.c1
Remote SQL: SELECT c1 FROM "S 1"."T 3" ORDER BY c1 ASC NULLS
LAST
Planning Time: 0.030 ms
Execution Time: 0.207 ms
(11 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Result (cost=100.23..100.24 rows=1 width=4) (actual time=0.832..0.832
rows=1.00 loops=1)
Output: (InitPlan minmax_1).col1
Replaces: MinMaxAggregate
InitPlan minmax_1
-> Limit (cost=100.00..100.23 rows=1 width=4) (actual time=0.831..0.831
rows=1.00 loops=1)
Output: ft4.c2
-> Foreign Scan on public.ft4 (cost=100.00..761.35 rows=2925
width=4) (actual time=0.831..0.831 rows=1.00 loops=1)
Output: ft4.c2
Remote SQL: SELECT c2 FROM "S 1"."T 3" ORDER BY c2 ASC NULLS
LAST
Planning Time: 0.029 ms
Execution Time: 0.900 ms
(11 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------
Foreign Scan (cost=100.32..100.54 rows=1 width=4) (actual time=0.130..0.130
rows=1.00 loops=1)
Output: (min(c1))
Relations: Aggregate on (public.ft4)
Remote SQL: SELECT min(c1) FROM "S 1"."T 3"
Planning Time: 0.315 ms
Execution Time: 0.163 ms
(6 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------
Foreign Scan (cost=280.00..280.22 rows=1 width=4) (actual time=0.803..0.803
rows=1.00 loops=1)
Output: (min(c2))
Relations: Aggregate on (public.ft4)
Remote SQL: SELECT min(c2) FROM "S 1"."T 3"
Planning Time: 0.316 ms
Execution Time: 0.837 ms
(6 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.32..0.33 rows=1 width=4) (actual time=0.012..0.012 rows=1.00
loops=1)
Output: (InitPlan minmax_1).col1
Replaces: MinMaxAggregate
Buffers: shared hit=3
InitPlan minmax_1
-> Limit (cost=0.29..0.32 rows=1 width=4) (actual time=0.011..0.011
rows=1.00 loops=1)
Output: "T 3".c1
Buffers: shared hit=3
-> Index Only Scan using t3_pkey on "S 1"."T 3" (cost=0.29..328.29
rows=10000 width=4) (actual time=0.011..0.011 rows=1.00 loops=1)
Output: "T 3".c1
Heap Fetches: 1
Index Searches: 1
Buffers: shared hit=3
Planning Time: 0.026 ms
Execution Time: 0.019 ms
(15 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=180.00..180.01 rows=1 width=4) (actual time=1.054..1.054
rows=1.00 loops=1)
Output: min(c2)
Buffers: shared hit=55
-> Seq Scan on "S 1"."T 3" (cost=0.00..155.00 rows=10000 width=4) (actual
time=0.004..0.412 rows=10000.00 loops=1)
Output: c1, c2, c3
Buffers: shared hit=55
Planning Time: 0.026 ms
Execution Time: 1.061 ms
(8 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------
Foreign Scan (cost=107.31..146.78 rows=1 width=4) (actual time=0.127..0.127
rows=1.00 loops=1)
Output: (min(c1))
Relations: Aggregate on (public.ft4)
Remote SQL: SELECT min(c1) FROM "S 1"."T 3"
Planning Time: 0.017 ms
Execution Time: 0.187 ms
(6 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------
Foreign Scan (cost=107.31..146.78 rows=1 width=4) (actual time=0.795..0.796
rows=1.00 loops=1)
Output: (min(c2))
Relations: Aggregate on (public.ft4)
Remote SQL: SELECT min(c2) FROM "S 1"."T 3"
Planning Time: 0.017 ms
Execution Time: 0.859 ms
(6 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------
Foreign Scan (cost=100.32..100.54 rows=1 width=4) (actual time=0.124..0.124
rows=1.00 loops=1)
Output: (min(c1))
Relations: Aggregate on (public.ft4)
Remote SQL: SELECT min(c1) FROM "S 1"."T 3"
Planning Time: 0.186 ms
Execution Time: 0.157 ms
(6 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------
Foreign Scan (cost=280.00..280.22 rows=1 width=4) (actual time=0.801..0.802
rows=1.00 loops=1)
Output: (min(c2))
Relations: Aggregate on (public.ft4)
Remote SQL: SELECT min(c2) FROM "S 1"."T 3"
Planning Time: 0.209 ms
Execution Time: 0.836 ms
(6 rows)
