From: Aaron Bono <postgresql@aranya.com>
To: Saad Anis <saad.anis@comtechmobile.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Breaking up a query
Date: Sat, 12 Aug 2006 11:22:16 -0500
Message-ID: <bf05e51c0608120922q1d984023ne42e0be12306fae5@mail.gmail.com> (raw)
In-Reply-To: <C1013130.34CA7%saad.anis@comtechmobile.com>
References: <C1013130.34CA7%saad.anis@comtechmobile.com>
On 8/10/06, Saad Anis <saad.anis@comtechmobile.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.comhttp://codeelixir.com
==================================================================
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: postgresql@aranya.com, saad.anis@comtechmobile.com
Subject: Re: Breaking up a query
In-Reply-To: <bf05e51c0608120922q1d984023ne42e0be12306fae5@mail.gmail.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