From: | Vladimir Ryabtsev <greatvovan(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Filtering on an enum field in a foreign table |
Date: | 2019-07-16 23:00:10 |
Message-ID: | CAMqTPqnBnHSN2=wJhY=bNqp3=_xnaHJTVg9HOqP7t3=8QeKCRg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Nikolay Samokhvalov | 2019-07-17 00:45:17 | Re: Filtering on an enum field in a foreign table |
Previous Message | Pavel Stehule | 2019-07-16 12:50:49 | Re: [HACKERS] proposal: schema variables |