Re: Performance killed with FDW when using CAST.

Funny that you mentioned that. I created the view a couple of hours ago and
that resolve the problem. The view had the order by on the timestamp as
well so it does not have to send the data over for sorting. I wish there
was a way for postgres to allow control of what happens on which server
when using foreign data wrappers. If there is I would like to know about it

On Wed, Apr 17, 2019, 15:40 Greg Spiegelberg <gspiegelberg(at)gmail(dot)com> wrote:

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