From: Saad Anis <saad.anis@comtechmobile.com>
To: pgsql-sql@postgresql.org
Subject: Breaking up a query
Date: Thu, 10 Aug 2006 18:53:33 -0400
Message-ID: <C1013130.34CA7%saad.anis@comtechmobile.com> (raw)
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;
Attachments:
ExplainAnalyzeOutput.txt (application/applefile, 500B) downloadExplainAnalyzeOutput.txt (text/plain, 3.3K) download
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: saad.anis@comtechmobile.com
Subject: Re: Breaking up a query
In-Reply-To: <C1013130.34CA7%saad.anis@comtechmobile.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox