Re: Breaking up a query - Mailing list pgsql-sql

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.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.