| From: | "David E(dot) Wheeler" <david(at)justatheory(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Reject Foreign Tables from MIN/MAX indexscan Optimization? |
| Date: | 2026-01-05 23:55:00 |
| Message-ID: | DD958FED-E259-4FD2-9371-A0EC2D24F7CE@justatheory.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hackers,
In working on a foreign data wrapper, I noticed that a simple `MIN()` or `MAX()` query doesn’t push down, even though the remote server supports it and it will push down for a more complicated query.
To demonstrate, I added this query to the postgres_fdw tests:
```
explain (verbose, costs off)
select min(c2) from ft4;
```
And the output:
```
QUERY PLAN
-----------------------------------------------------------------------------------
Result
Output: (InitPlan minmax_1).col1
Replaces: MinMaxAggregate
InitPlan minmax_1
-> Limit
Output: ft4.c2
-> Foreign Scan on public.ft4
Output: ft4.c2
Remote SQL: SELECT c2 FROM "S 1"."T 3" ORDER BY c2 ASC NULLS LAST
(9 rows)
```
I don’t think this is what we want, but should let the remote server handle the optimization; besides the LIMIT isn’t pushed down!
The attached patch fixes the issue by disabling the MIN/MAX indexscan optimization for foreign tables. It simply does
```
/*
* Reject foreign tables. They have their own optimizations, so just let
* them have it.
*/
if (rte->relkind == RELKIND_FOREIGN_TABLE)
return;
```
With that change, the test out emits:
```
QUERY PLAN
-----------------------------------------------
Foreign Scan
Output: (min(c2))
Relations: Aggregate on (public.ft4)
Remote SQL: SELECT min(c2) FROM "S 1"."T 3"
(4 rows)
```
Leaving the optimization up to the remote server.
What do you think?
Best,
David
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Disable-MIN-MAX-optimization-for-foreign-tables.patch | application/octet-stream | 4.4 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Thomas Munro | 2026-01-06 00:09:02 | Re: LLVM JITLink attempt II (WIP) |
| Previous Message | Tristan Partin | 2026-01-05 23:53:42 | Re: [PATCH] meson: Update meson to enable building postgres as a subproject |