Re: Performance killed with FDW when using CAST.

Thanks for the info. Is there a way to match the collations and make it
work?

On Wed, Apr 17, 2019, 07:02 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Thomas Kellerer <spam_eater(at)gmx(dot)net> writes:
> > Laurenz Albe schrieb am 17.04.2019 um 07:03:
> >> After debugging into this, it seems that the hstore operator -> cannot
> >> be pushed down because of collation problems.
>
> > Do you happen to know if the JSONB operator -> (or ->>) can be pushed
> down?
>
> A bit of experimentation says that jsonb -> integer can be pushed down,
> but not any of the variants involving a text fieldname or result.
> Presumably this is because of the heuristic that says not to push down
> a collation that didn't arise from the remote column. jsonb -> text
> isn't really collation-sensitive, of course, but postgres_fdw has no
> good way to know that, since the core code (outside of that operator
> itself) doesn't know it either. The assumption is that any function
> with at least one input of a collatable type is collation-sensitive.
> Here you're getting a default collation from the text literal, and
> postgres_fdw doesn't want to assume that the remote end would choose
> the same collation.
>
> regards, tom lane
>
>
>