Re: Performance killed with FDW when using CAST.

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.

Thanks

On Tue, Apr 16, 2019 at 6:56 PM Ian Barwick <ian(dot)barwick(at)2ndquadrant(dot)com>
wrote:

> On 4/17/19 9:21 AM, Jorge Torralba wrote:
> > We setup a FDW server for a large table that has an hstore data type as
> on eof the coums.
> >
> > When we run the query directly on the FDW server
> >
> > select * from recent_events where account_id = 1 AND (attributes ->
> 'account_incident_id')::integer = 2617116 limit 10;
> >
> > The response is 2ms . which is awesome.
> >
> > When we run it from the remote server where we have the server defined,
> the query never returns.
> >
> > Our server definition is as follows.
> >
> > (host 'fdwserver', dbname 'mydb', port '5432', fetch_size '1000',
> use_remote_estimate 'true', extensions 'hstore')
> >
> > We have been playing with this for hours to no avail.
> >
> > Any clues ?
>
> At a guess the FDW is not able to push down the entire query to the FDW
> server
> and might be doing something like fetching all the remote rows and casting
> them locally, or something along those lines.
>
> It would help to provide the following info:
>
> - PostgreSQL versions of both servers
> - "EXPLAIN" output of the query on both servers
> - "EXPLAIN ANALYZE" output of the query executed directly on the FDW server
> - approximate number of rows in the "recent_events" table
>
>
> Regards
>
> Ian Barwick
>
> --
> Ian Barwick https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

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