Filtering on an enum field in a foreign table

Vladimir Ryabtsev <[email protected]>
Newsgroups gmane.comp.db.postgresql.performance
Message-ID <CAMqTPqnBnHSN2=wJhY=bNqp3=_xnaHJTVg9HOqP7t3=8QeKCRg@mail.gmail.com>
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
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.