Re: Performance killed with FDW when using CAST.

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(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
>
>
>

--
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.