Reject Foreign Tables from MIN/MAX indexscan Optimization?

Поиск
Список
Период
Сортировка
От David E. Wheeler
Тема Reject Foreign Tables from MIN/MAX indexscan Optimization?
Дата
Msg-id DD958FED-E259-4FD2-9371-A0EC2D24F7CE@justatheory.com
обсуждение исходный текст
Ответы Re: Reject Foreign Tables from MIN/MAX indexscan Optimization?
Список 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
theremote 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
pusheddown! 

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


Вложения

В списке pgsql-hackers по дате отправления: