X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 614B29FB226 for ; Sat, 12 Aug 2006 13:22:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 28061-08 for ; Sat, 12 Aug 2006 13:22:18 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.189]) by postgresql.org (Postfix) with ESMTP id 3D60C9FB20F for ; Sat, 12 Aug 2006 13:22:17 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id n28so1404798nfc for ; Sat, 12 Aug 2006 09:22:17 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=uun/XDbNcA/asLpXwwfbb1pF69mq1ko7kPK6Imf9Vz98KMuLepI0P9X52eaidhHH+LETYn740ixboWAXUys6eS6vYlE2m2kO6g/iF92Y2UdxaspOGx7DiI2PxfweN4ktRKzbvyGfX/UugPkgKwjHJmKjfjcbjBbgkiuUW4uGaCw= Received: by 10.78.170.6 with SMTP id s6mr2626839hue; Sat, 12 Aug 2006 09:22:16 -0700 (PDT) Received: by 10.78.32.10 with HTTP; Sat, 12 Aug 2006 09:22:16 -0700 (PDT) Message-ID: Date: Sat, 12 Aug 2006 11:22:16 -0500 From: "Aaron Bono" To: "Saad Anis" Subject: Re: Breaking up a query Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_87612_14738295.1155399736212" References: X-Google-Sender-Auth: 54c52eaa7892f97b X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.751 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_40_50, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/92 X-Sequence-Number: 26050 ------=_Part_87612_14738295.1155399736212 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/10/06, 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. > > 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 ================================================================== ------=_Part_87612_14738295.1155399736212 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.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.

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