Reject Foreign Tables from MIN/MAX indexscan Optimization?

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

Responses

Browse pgsql-hackers by date

  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