[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: Re: Performance killed with FDW when using CAST.
From: Jorge Torralba <jorge.torralba () gmail ! com>
Date: 2019-04-17 23:23:00
Message-ID: CACut7uQXgLu6o2sjq5rmfsq5phVg1UJgwLWKaKm1QE2s7EZ-fA () mail ! gmail ! com
[Download RAW message or body]
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@gmail.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.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.
>>
>
[Attachment #3 (text/html)]
<div dir="auto">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</div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Apr 17, 2019, 15:40 \
Greg Spiegelberg <<a \
href="mailto:gspiegelberg@gmail.com">gspiegelberg@gmail.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div>Hi \
Jorge,</div><div><br></div><div>Can you create a view in the source database such \
as</div><div>CREATE VIEW xxx_id_attributes AS</div><div>SELECT id, \
CAST(attributes->>'account_incident_id' AS integer)</div><div> FROM \
xxx WHERE attributes->'account_incident_id' ~ \
'^[0-9]+$';</div><div><br></div><div>On the remote server, create a foreign \
table on the new view and perform your test.</div><div><br></div><div>Just \
curious.</div><div><br></div><div>-Greg</div><br><div class="gmail_quote"><div \
dir="ltr" class="gmail_attr">On Wed, Apr 17, 2019 at 2:59 PM Jorge Torralba <<a \
href="mailto:jorge.torralba@gmail.com" target="_blank" \
rel="noreferrer">jorge.torralba@gmail.com</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr">I made a copy of the \
table and altered the column from hstore to jsonb.<div><br></div><div>Ran the \
following query with the same performance issues. </div><div><br></div><div>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;<br></div><div><br></div><div>Things to Note.</div><div><br></div><div>Remove the \
CAST on the attributes column and the order by results in quick \
performance</div><div><br></div><div>Add order by performance \
dies</div><div><br></div><div>Add CAST without the order by you can go out for dinner \
and still be waiting for a result \
set.</div><div><br></div><div><br></div><div><br></div><div><br></div></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Apr 17, 2019 at 7:02 AM \
Tom Lane <<a href="mailto:tgl@sss.pgh.pa.us" target="_blank" \
rel="noreferrer">tgl@sss.pgh.pa.us</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">Thomas Kellerer <<a \
href="mailto:spam_eater@gmx.net" target="_blank" \
rel="noreferrer">spam_eater@gmx.net</a>> writes:<br> > Laurenz Albe schrieb am \
17.04.2019 um 07:03:<br> >> After debugging into this, it seems that the hstore \
operator -> cannot<br> >> be pushed down because of collation problems.<br>
<br>
> Do you happen to know if the JSONB operator -> (or ->>) can be pushed \
down? <br> <br>
A bit of experimentation says that jsonb -> integer can be pushed down,<br>
but not any of the variants involving a text fieldname or result.<br>
Presumably this is because of the heuristic that says not to push down<br>
a collation that didn't arise from the remote column. jsonb -> text<br>
isn't really collation-sensitive, of course, but postgres_fdw has no<br>
good way to know that, since the core code (outside of that operator<br>
itself) doesn't know it either. The assumption is that any function<br>
with at least one input of a collatable type is collation-sensitive.<br>
Here you're getting a default collation from the text literal, and<br>
postgres_fdw doesn't want to assume that the remote end would choose<br>
the same collation.<br>
<br>
regards, tom lane<br>
<br>
<br>
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" \
class="m_-5204531660410963695gmail-m_9203184593026580418gmail_signature">Thanks,<br><br>Jorge \
Torralba<br>----------------------------<br><br>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.</div> </blockquote></div></div>
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic