Re: Performance killed with FDW when using CAST. - Mailing list pgsql-admin

Hi Jorge,

Can you create a view in the source database such as

CREATE VIEW xxx_id_attributes AS

SELECT id, CAST(attributes->>'account_incident_id' AS integer)

  FROM xxx WHERE attributes->'account_incident_id' ~ '^[0-9]+$';

On the remote server, create a foreign table on the new view and perform your test.

Just curious.

-Greg

On Wed, Apr 17, 2019 at 2:59 PM Jorge Torralba <jorge.torralba@gmail.com> wrote:

I made a copy of the table and altered the column from hstore to jsonb.

Ran the following query with the same performance issues. 

SELECT id, attributes FROM xxx WHERE account_id = 1 AND timestamp >= '2019-01-16 22:34:28.584' AND  CAST(attributes ->> 'account_incident_id' as integer) = 2617116  order by timestamp desc limit 10;

Things to Note.

Remove the CAST on the attributes column and the order by results in quick performance

Add order by performance dies

Add CAST without the order by you can go out for dinner and still be waiting for a result set.

On Wed, Apr 17, 2019 at 7:02 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

--

Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.