Thread: Filtering on an enum field in a foreign table
I am querying a remote server through a foreign table definition.
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
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).
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
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 likeselect * 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)?
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).
"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 likeselect * 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)?
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 <greatvovan@gmail.com>:
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 likeselect * 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)?