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 <samokhvalov@gmail.com>:

Hi,

On Tue, Jul 16, 2019 at 4:00 PM Vladimir Ryabtsev <greatvovan@gmail.com> 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)?