Re: Performance killed with FDW when using CAST.

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(dot)torralba(at)gmail(dot)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(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.
>