From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas Kellerer <spam_eater@gmx.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: Performance killed with FDW when using CAST.
Date: Wed, 17 Apr 2019 10:02:10 -0400
Message-ID: <28623.1555509730@sss.pgh.pa.us> (raw)
In-Reply-To: <q96go6$6fn4$1@blaine.gmane.org>
References: <CACut7uSz_3JiJ4w80VrQYECtOAt1Ya5E=EdLBAJ2_DnYwqsjGA@mail.gmail.com>
<ea2a08fe7a53481ae973dadaf174dc2bf6e89a8e.camel@cybertec.at>
<q96go6$6fn4$1@blaine.gmane.org>
Thomas Kellerer <spam_eater@gmx.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, spam_eater@gmx.net
Subject: Re: Performance killed with FDW when using CAST.
In-Reply-To: <28623.1555509730@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox