PostgreSQL – WHERE Condition Not Pushed to Remote in Postgres RDW

postgresqlpostgresql-fdw

I have a remote postgres table using RDW. It contains a JSONB column and I use values in that JSONB for the WHERE condition.

The remote table has a GIN index on the JSONB column and an index on the hostname-attribute that I use for filtering. 

EXPLAIN ANALYZE VERBOSE
SELECT 
* 
FROM forein_table
WHERE details->>'hostname' = 'host-xyz'

shows that the "Remote SQL" does not contain the WHERE-clause and filtering is done local.
On the other hand

EXPLAIN ANALYZE VERBOSE
SELECT 
* 
FROM forein_table
WHERE details@> '{"hostname": "host-xyz"}'

sends down the 'WHERE' clause which is obviously much faster.

The documentation says:

WHERE clauses are not sent to the remote server unless they use only built-in data types, operators, and functions. Operators and functions in the clauses must be IMMUTABLE as well.

Is there a difference between ->> and @>?

Is there a way to use WHERE details->>'hostname' = 'host-xyz' on a remote table and have it push down the WHERE clause?

(I found a way by creating a VIEW which extracts the hostname attribute as a column, and then creating the remote table on this view…but obviously this is not a very flexible/elegant approach)

Best Answer

I believe the reason for this is that in details->>'hostname', 'hostname' is of a text type, and PostgreSQL cannot prove to itself either that the same default collation is in use on both sides of the FDW, or that the ->> operator will always return the same answer regardless of collation of its right argument. So it won't push it over.

There has been some talk of adding a way to mark functions and operators as not being sensitive to collation. But that won't done until at v12 at the earliest.

Related Question