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.
