pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBreaking up a query
4+ messages / 4 participants
[nested] [flat]
* Breaking up a query
@ 2006-08-10 22:53 Saad Anis <saad.anis@comtechmobile.com>
2006-08-12 16:22 ` Re: Breaking up a query Aaron Bono <postgresql@aranya.com>
2006-08-13 14:43 ` Re: Breaking up a query Andrew Sullivan <ajs@crankycanuck.ca>
2006-08-15 14:16 ` Re: Breaking up a query Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 3 replies; 4+ messages in thread
From: Saad Anis @ 2006-08-10 22:53 UTC (permalink / raw)
To: 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;
Attachments:
ExplainAnalyzeOutput.txt (application/applefile, 500B) download
ExplainAnalyzeOutput.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)
* Re: Breaking up a query
2006-08-10 22:53 Breaking up a query Saad Anis <saad.anis@comtechmobile.com>
@ 2006-08-12 16:22 ` Aaron Bono <postgresql@aranya.com>
2 siblings, 0 replies; 4+ messages in thread
From: Aaron Bono @ 2006-08-12 16:22 UTC (permalink / raw)
To: Saad Anis <saad.anis@comtechmobile.com>; +Cc: pgsql-sql
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.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Breaking up a query
2006-08-10 22:53 Breaking up a query Saad Anis <saad.anis@comtechmobile.com>
@ 2006-08-13 14:43 ` Andrew Sullivan <ajs@crankycanuck.ca>
2 siblings, 0 replies; 4+ messages in thread
From: Andrew Sullivan @ 2006-08-13 14:43 UTC (permalink / raw)
To: pgsql-sql
On Thu, Aug 10, 2006 at 06:53:33PM -0400, Saad Anis 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
Why is that obvious? Why do you think that joining in your
application is going to give better performance than joining in the
database? This is what RDBMS _do_, for heaven's sake.
(I agree that your plan shows it's taking a long time. But that's a
different problem. We don't actually know what you want from your
query.)
A
--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Breaking up a query
2006-08-10 22:53 Breaking up a query Saad Anis <saad.anis@comtechmobile.com>
@ 2006-08-15 14:16 ` Scott Marlowe <smarlowe@g2switchworks.com>
2 siblings, 0 replies; 4+ messages in thread
From: Scott Marlowe @ 2006-08-15 14:16 UTC (permalink / raw)
To: Saad Anis <saad.anis@comtechmobile.com>; +Cc: pgsql-sql
On Thu, 2006-08-10 at 17:53, Saad Anis 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.
Nope, that's not true in PostgreSQL. It is for some databases with
relatively simplistic query planners, but not postgresql.
I'd check that you have indexes where you need them (generally when you
see a seq scan on a small set) including, especially, the foreign key
columns (i.e. the ones pointing to another table's primary keys).
On to your explain analyze, I noticed a lot of lines like this:
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 loop
seeing as how the statistical default for a new, unanalyzed table is
1000, and you've got 1000 peppered all through your explain analyze, I'd
guess you've not analyzed your database. Which means you've likely not
read the admin docs. which means you've likely not vacuumed the
database.
Read the admin docs (they're not that thick, and there's lots of good
info in there) and apply things like vacuum and analyze, and get back to
us on how things are doing then.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-08-15 14:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-10 22:53 Breaking up a query Saad Anis <saad.anis@comtechmobile.com>
2006-08-12 16:22 ` Aaron Bono <postgresql@aranya.com>
2006-08-13 14:43 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-08-15 14:16 ` Scott Marlowe <smarlowe@g2switchworks.com>
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