Hi Guys,
A fellow at work has written the SQL below to retrieve some data from
multiple tables. Obviously it is inefficient and unnecessarily complex, and
I am trying to break it into 2 or more queries so as to enhance performance.
Can you please help me do so? I would appreciate any help you can provide.
I have also attached the output of the "explain analyze" of this query.
Thank you.
Saad
SELECT v.xcvr_id as xcvr_id
, v.bumper_number as bumper_number
, v.vehicle_type as vehicle_type
, p.epoch as epoch
, p.latitude as latitude
, p.longitude as longitude
, p.fom as fom
, i.version as version
, i.rfid_status as rfid_status
, t.tag_id as tag_id
, t.tag_status as tag_status
FROM positions p
LEFT OUTER JOIN data_transfers dt
ON p.id = dt.position_id
INNER JOIN vehicles v
ON p.vehicle_id = v.id
LEFT OUTER JOIN interrogations i
ON p.id = i.position_id
AND v.id = i.vehicle_id
LEFT OUTER JOIN tags t
ON i.id = t.interrogation_id
WHERE p.id NOT IN (
SELECT dt.position_id
FROM data_transfers
WHERE dt.target_id = ?
)
ORDER BY v.xcvr_id
, v.bumper_number
, v.vehicle_type
, i.version
, i.rfid_status
, p.epoch;
My guess is your problem is occurring here:
-> Merge Left Join (cost=0.00..11334.00 rows=1000 width=28) (actual time=705.104..706.789 rows=55 loops=1)
Merge Cond: ("outer".id = "inner".position_id)
Filter: (NOT (subplan))
-> Index Scan using positions_pkey on positions p (cost=0.00..32.00 rows=1000 width=28) (actual time=0.019..90.920 rows=13958 loops=1)
-> Index Scan using data_transfers_position_id_idx on data_transfers dt (cost= 0.00..32.00 rows=1000 width=8) (actual time=0.015..91.859 rows=13903 loops=1)
You see that the cost jumps significantly.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================