Re: Filtering on an enum field in a foreign table

Wait folks,

I realized that if I create a basic view with enum fields converted to
text, it does the trick! I query the view with text predicate and the view
implementation (I think) converts it into enums when querying the
underlying table.

But I still think it should work without such workarounds...

вт, 16 июл. 2019 г. в 18:06, Vladimir Ryabtsev <[email protected]>:

> 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)?
>>
>>
>>
>