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)
