Re: Breaking up a query

On 8/10/06, Saad Anis <saad(dot)anis(at)comtechmobile(dot)com> wrote:
>
> 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;

On an surface scan of what you have I don't see anything obviously wrong.
Do you have your foreign keys defined along all the joins? What kind of
indexes do you have defined on the tables?

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.00rows=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.859rows=13903 loops=1)

You see that the cost jumps significantly.

==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================