Filtering on an enum field in a foreign table
I am querying a remote server through a foreign table definition.
CREATE TABLE example (id integer, product product_enum, status status_enum)
Where
CREATE TYPE status AS ENUM ('active', 'testing', 'inactive', ...);
CREATE TYPE product AS ENUM ('a', 'b', 'c', ...);
I re-created enums on my server and created a foreign table as follows:
CREATE FOREIGN TABLE example (id integer, product product_enum, status
status_enum)
SERVER remote;
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).
I tried to cheat this thing by defining the enum fields as text in the
foreign table but then the remote query fails with
ERROR: operator does not exist: public.product = text HINT: No operator
matches the given name and argument type(s). You might need to add explicit
type casts.
This is ridiculous. Is there a way to workaround this and force it execute
the remote query as is?
Regards,
Vlad
Re: Filtering on an enum field in a foreign table
Hi,
On Tue, Jul 16, 2019 at 4:00 PM Vladimir Ryabtsev
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)?
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 :
> Hi,
>
> On Tue, Jul 16, 2019 at 4:00 PM Vladimir Ryabtsev
> 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)?
>
>
>
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 :
> 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 :
>
>> Hi,
>>
>> On Tue, Jul 16, 2019 at 4:00 PM Vladimir Ryabtsev
>> 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)?
>>
>>
>>
>
