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;
ExplainAnalyzeOutput.txt
Description: application/applefile
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=11657.83..11660.33 rows=1000 width=113) (actual
time=7315.311..7323.124 rows=1487 loops=1)
Sort Key: v.xcvr_id, v.bumper_number, v.vehicle_type, i."version",
i.rfid_status, p.epoch
-> Merge Left Join (cost=11588.00..11608.00 rows=1000 width=113) (actual
time=7275.853..7302.973 rows=1487 loops=1)
Merge Cond: ("outer".id = "inner".interrogation_id)
-> Sort (cost=11518.17..11520.67 rows=1000 width=109) (actual
time=878.566..878.858 rows=55 loops=1)
Sort Key: i.id
-> Hash Left Join (cost=11408.83..11468.34 rows=1000
width=109) (actual time=876.369..878.208 rows=55 loops=1)
Hash Cond: (("outer".id = "inner".position_id) AND
("outer".id = "inner".vehicle_id))
-> Merge Join (cost=11383.83..11433.33 rows=1000
width=101) (actual time=707.190..708.292 rows=55 loops=1)
Merge Cond: ("outer".id = "inner".vehicle_id)
-> Index Scan using vehicles_pkey on vehicles v
(cost=0.00..32.00 rows=1000 width=77) (actual time=0.023..0.093 rows=10 loops=1)
-> Sort (cost=11383.83..11386.33 rows=1000
width=28) (actual time=707.134..707.423 rows=55 loops=1)
Sort Key: p.vehicle_id
-> 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)
SubPlan
-> Result (cost=0.00..20.00
rows=1000 width=0) (actual time=0.017..0.017 rows=1 loops=13958)
One-Time Filter: ($1 = 1)
-> Seq Scan on data_transfers
(cost=0.00..20.00 rows=1000 width=0) (actual time=0.006..0.006 rows=1
loops=13903)
-> Hash (cost=20.00..20.00 rows=1000 width=24) (actual
time=168.317..168.317 rows=0 loops=1)
-> Seq Scan on interrogations i (cost=0.00..20.00
rows=1000 width=24) (actual time=0.021..85.388 rows=13958 loops=1)
-> Sort (cost=69.83..72.33 rows=1000 width=12) (actual
time=3334.230..4828.854 rows=284471 loops=1)
Sort Key: t.interrogation_id
-> Seq Scan on tags t (cost=0.00..20.00 rows=1000 width=12)
(actual time=0.009..1587.663 rows=284471 loops=1)
Total runtime: 7355.254 ms
(28 rows)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
