I have been trying to speed up my psql queries to squeeze out as much speed as possible. With a few indexes I installed on my local system I got good speeds. I installed these on the remote system but had different results. The screenshot for the planners follow:
Local Planner:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=19.54..67.37 rows=12 width=133) (actual time=0.771..0.862 rows=12 loops=1)
Hash Cond: ((sensor_lookup.sensorid)::text = (sensor.sensorid)::text)
Buffers: shared hit=25
-> Nested Loop (cost=3.01..50.81 rows=12 width=119) (actual time=0.193..0.271 rows=12 loops=1)
Buffers: shared hit=19
-> Nested Loop (cost=2.60..26.10 rows=1 width=320) (actual time=0.163..0.228 rows=1 loops=1)
Buffers: shared hit=15
-> Nested Loop (cost=2.60..25.02 rows=1 width=98) (actual time=0.156..0.217 rows=1 loops=1)
Buffers: shared hit=14
-> Nested Loop (cost=0.27..13.80 rows=1 width=68) (actual time=0.097..0.151 rows=1 loops=1)
Buffers: shared hit=7
-> Index Scan using meta_pkey on meta (cost=0.27..4.29 rows=1 width=45) (actual time=0.029..0.031 rows=1 loops=1)
Index Cond: (stationid = 'WYTOR02'::bpchar)
Buffers: shared hit=3
-> Seq Scan on meta_lookup (cost=0.00..9.50 rows=1 width=31) (actual time=0.064..0.116 rows=1 loops=1)
Filter: ((stationid)::bpchar = 'WYTOR02'::bpchar)
Rows Removed by Filter: 439
Buffers: shared hit=4
-> Bitmap Heap Scan on datetime_lookup (cost=2.33..11.21 rows=1 width=38) (actual time=0.054..0.060 rows=1 loops=1)
Recheck Cond: (stationid = 'WYTOR02'::bpchar)
Filter: ((productid)::text = 'qc60'::text)
Rows Removed by Filter: 5
Heap Blocks: exact=5
Buffers: shared hit=7
-> Bitmap Index Scan on idx_16 (cost=0.00..2.32 rows=6 width=0) (actual time=0.033..0.033 rows=6 loops=1)
Index Cond: (stationid = 'WYTOR02'::bpchar)
Buffers: shared hit=2
-> Seq Scan on product (cost=0.00..1.07 rows=1 width=222) (actual time=0.006..0.008 rows=1 loops=1)
Filter: ((productid)::text = 'qc60'::text)
Rows Removed by Filter: 5
Buffers: shared hit=1
-> Index Scan using idx_15 on sensor_lookup (cost=0.41..24.59 rows=12 width=30) (actual time=0.027..0.034 rows=12 loops=1)
Index Cond: ((stationid = 'WYTOR02'::bpchar) AND ((productid)::text = 'qc60'::text))
Buffers: shared hit=4
-> Hash (cost=10.68..10.68 rows=468 width=27) (actual time=0.547..0.548 rows=468 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 34kB
Buffers: shared hit=6
-> Seq Scan on sensor (cost=0.00..10.68 rows=468 width=27) (actual time=0.013..0.208 rows=468 loops=1)
Buffers: shared hit=6
Planning time: 1.655 ms
Execution time: 1.106 ms
Remote Planner:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=26.67..102.51 rows=12 width=133) (actual time=0.644..0.719 rows=12 loops=1)
Hash Cond: ((sensor_lookup.sensorid)::text = (sensor.sensorid)::text)
Buffers: shared hit=29
-> Nested Loop (cost=9.14..84.82 rows=12 width=119) (actual time=0.161..0.227 rows=12 loops=1)
Buffers: shared hit=19
-> Nested Loop (cost=4.60..38.12 rows=1 width=108) (actual time=0.128..0.187 rows=1 loops=1)
Buffers: shared hit=15
-> Nested Loop (cost=4.60..37.03 rows=1 width=98) (actual time=0.116..0.173 rows=1 loops=1)
Buffers: shared hit=14
-> Nested Loop (cost=0.27..17.80 rows=1 width=68) (actual time=0.081..0.132 rows=1 loops=1)
Buffers: shared hit=7
-> Index Scan using meta_pkey on meta (cost=0.27..8
.29 rows=1 width=45) (actual time=0.011..0.012 rows=1 loops=1)
Index Cond: (stationid = 'WYTOR02'::bpchar)
Buffers: shared hit=3
-> Seq Scan on meta_lookup (cost=0.00..9.50 rows=1 width=31) (actual time=0.067..0.117 rows=1 loops=1)
Filter: ((stationid)::bpchar = 'WYTOR02'::bpchar)
Rows Removed by Filter: 439
Buffers: shared hit=4
-> Bitmap Heap Scan on datetime_lookup (cost=4.33..19.22 rows=1 width=38) (actual time=0.031..0.036 rows=1 loops=1)
Recheck Cond: (stationid = 'WYTOR02'::bpchar)
Filter: ((productid)::text = 'qc60'::text)
Rows Removed by Filter: 5
Heap Blocks: exact=5
Buffers: shared hit=7
-> Bitmap Index Scan on idx_16 (cost=0.00..4.33 rows=6 width=0) (actual time=0.019..0.019 rows=6 loops=1)
Index Cond: (stationid = 'WYTOR02'::bpchar)
Buffers: shared hit=2
-> Seq Scan on product (cost=0.00..1.07 rows=1 width=10) (actual time=0.010..0.012 rows=1 loops=1)
Filter: ((productid)::text = 'qc60'::text)
Rows Removed by Filter: 5
Buffers: shared hit=1
-> Bitmap Heap Scan on sensor_lookup (cost=4.54..46.58 rows=12 width=30) (actual time=0.030..0.032 rows=12 loops=1)
Recheck Cond: ((stationid = 'WYTOR02'::bpchar) AND ((productid)::text = 'qc60'::text))
Heap Blocks: exact=1
Buffers: shared hit=4
-> Bitmap Index Scan on idx_15 (cost=0.00..4.54 rows=12 width=0) (actual time=0.021..0.021 rows=12 loops=1)
Index Cond: ((stationid = 'WYTOR02'::bpchar) AND ((productid)::text = 'qc60'::text))
Buffers: shared hit=3
-> Hash (cost=11.68..11.68 rows=468 width=27) (actual time=0.440..0.440 rows=468 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 34kB
Buffers: shared hit=7
-> Seq Scan on sensor (cost=0.00..11.68 rows=468 width=27) (actual time=0.004..0.174 rows=468 loops=1)
Buffers: shared hit=7
Planning time: 2.572 ms
Execution time: 0.947 ms
Even though the difference is 1ms, these calls are done thousands of time so the difference adds up. The difference seems to ne that the reomote is doing a Bitmap Heap Scan as opposed to an Index Scan. Though I'm not sure these differences account for the planning time it is a difference between matching systems. The settings in the postgresql.conf are the same so what can I look at to see why these are different?
Both the local and remote servers have the same Postgresql and Ubuntu versions: Ubuntu 18.04.1 psql (PostgreSQL) 10.15 (Ubuntu 10.15-0ubuntu0.18.04.1)