Re: Performance killed with FDW when using CAST.

I tried without casting and still the same problem on the server accessing
remote data.

On the server hosting the data, even with casting the query executes in
78ms.

fty, I have run analyze on both the servers as well.

Thanks

JT

On Tue, Apr 16, 2019 at 8:00 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:

>
>
> On 4/16/19 9:40 PM, Jorge Torralba wrote:
> > Thanks for taking the time to look.
> >
> > Both servers are on ....
> >
> > version
> >
> ----------------------------------------------------------------------------------------------------------------------------------
> > PostgreSQL 11.2 (Debian 11.2-1.pgdg90+1) on x86_64-pc-linux-gnu,
> compiled
> > by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
> >
> > I have reduced the query to select only two columns for simplicity with
> > the same results.
> >
> > The table contain about 50 million rows
> >
> > On the server hosting the table ..... Hiding private data.
> >
> > alertsdb_recent_events=# select id, attributes -> 'account_incident_id'
> > from recent_events where account_id = 1 AND (attributes ->
> > 'account_incident_id')::integer = 2617116 limit 5;
> > id | ?column?
> > --------------------------------------+----------
> > ***** | 2617116
> > ***** | 2617116
> > ***** | 2617116
> > ***** | 2617116
> > ***** | 2617116
> > (5 rows)
> >
> > Time: 82.868 ms
> >
> >
> > # explain select id, attributes -> 'account_incident_id' from
> > recent_events where account_id = 1 AND (attributes ->
> > 'account_incident_id')::integer = 2617116 limit 5;
> > QUERY PLAN
> >
> -----------------------------------------------------------------------------------------------------------------------------------------
> > Limit (cost=0.56..3.77 rows=5 width=48)
> > -> Index Scan using
> > recent_event_account_id_attributes_account_incident_id_idx on
> > recent_events (cost=0.56..56.31 rows=87 width=48)
> > Index Cond: ((account_id = 1) AND (((attributes ->
> > 'account_incident_id'::text))::integer = 2617116))
> > (3 rows)
> >
> > Time: 71.907 ms
> >
> > # explain analyze select id, attributes -> 'account_incident_id' from
> > recent_events where account_id = 1 AND (attributes ->
> > 'account_incident_id')::integer = 2617116 limit 5;
> > QUERY PLAN
> >
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> > Limit (cost=0.56..3.77 rows=5 width=48) (actual time=0.019..0.025
> rows=5
> > loops=1)
> > -> Index Scan using
> > recent_event_account_id_attributes_account_incident_id_idx on
> > recent_events (cost=0.56..56.31 rows=87 width=48) (actual
> > time=0.018..0.023 rows=5 loops=1)
> > Index Cond: ((account_id = 1) AND (((attributes ->
> > 'account_incident_id'::text))::integer = 2617116))
> > Planning Time: 0.124 ms
> > Execution Time: 0.038 ms
> > (5 rows)
> >
> > Time: 80.782 ms
> >
> >
> > On the server that communicates with the FDW server .....
> >
> > # explain select id, attributes -> 'account_incident_id' from
> > recent_events where account_id = 1 AND (attributes ->
> > 'account_incident_id')::integer = 2617116 limit 5;
> > QUERY PLAN
> >
> -------------------------------------------------------------------------------------
> > Limit (cost=100.00..426.27 rows=5 width=48)
> > -> Foreign Scan on recent_events (cost=100.00..6663659.61
> rows=102117
> > width=48)
> > Filter: (((attributes -> 'account_incident_id'::text))::integer
> =
> > 2617116)
> > (3 rows)
> >
> > Time: 85.276 ms
> >
> >
> > # explain analyze select id, attributes -> 'account_incident_id' from
> > recent_events where account_id = 1 AND (attributes ->
> > 'account_incident_id')::integer = 2617116 limit 5;
> > QUERY PLAN
> >
> ---------------------------------------------------------------------------------------------------------------------------------------
> > Limit (cost=100.00..426.27 rows=5 width=48) (actual
> > time=21242.087..26387.257 rows=5 loops=1)
> > -> Foreign Scan on recent_events (cost=100.00..6663703.90
> rows=102117
> > width=48) (actual time=21242.086..26387.252 rows=5 loops=1)
> > Filter: (((attributes -> 'account_incident_id'::text))::integer
> =
> > 2617116)
> > Rows Removed by Filter: 724249
> > Planning Time: 1.164 ms
> > Execution Time: 26387.851 ms
> > (6 rows)
> >
> > Time: 26528.113 ms (00:26.528)
> >
> > The query killer is the ...
> >
> > AND (attributes -> 'account_incident_id')::integer = 2617116
> >
> > Run the query this way ...
> >
> > select id, attributes -> 'account_incident_id' from recent_events where
> > account_id = 1 limit 5;
> >
> > and the results is only 10ms slower than on the hosting server directly
> > which is what we are expecting. It's like the casting of the hstore
> > column is just not playing nice.
>
> Casting the left side of a predicate is not recommended. What if you cast
> 2617166 to be the same type as attributes -> 'account_incident_id'?
>
>
> --
> Angular momentum makes the world go 'round.
>
>
>

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