Re: Filtering on an enum field in a foreign table

Sorry, the version() is

"PostgreSQL 10.3 (Ubuntu 10.3-1.pgdg14.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit"

I gave use_remote_estimate a try but unfortunately it is the same.

Additionally I see on your page (in "Remote Execution Options"):

"By default, only WHERE clauses using built-in operators and functions will
be considered for execution on the remote server. Clauses involving
non-built-in functions are checked locally after rows are fetched."

I think enum types somehow fall into the same category and they are
filtered only locally, which is seen in the plan (Filter clause).
If I use only columns of built-in type in the predicate everything works as
expected (with filtering on the remote server).

I need a workaround to make this query execute remotely. One option may be
using a materialized view with these enum values converted to text but then
I will need to refresh this view periodically on the remote server.
And actually it looks like a performance bug in the DBMS...


вт, 16 июл. 2019 г. в 17:45, Nikolay Samokhvalov <[email protected]>:

> Hi,
>
> On Tue, Jul 16, 2019 at 4:00 PM Vladimir Ryabtsev <[email protected]>
> wrote:
>
>> I am querying a remote server through a foreign table definition.
>>
>> CREATE TABLE example (id integer, product product_enum, status
>> status_enum)
>>
> ...
>
>> When I am querying the foreign table on enum predicate like
>>
>> select * from example where product = 'a' and status = 'active'
>>
>> I see that filtering happens on my server which can be seen in the plan
>> and can be felt from the query performance (indices are not used of course).
>>
>
> What Postgres version do you use?
>
> Any changes in plans if you collect stats on the FDW table ("analyze
> example;")?
>
> Have you considered changing the option "use_remote_estimate" (see
> https://www.postgresql.org/docs/current/postgres-fdw.html#id-1.11.7.42.10)?
>
>
>