X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 5BD1E9FB27E for ; Thu, 10 Aug 2006 20:13:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 33452-04 for ; Thu, 10 Aug 2006 23:13:09 +0000 (UTC) X-Greylist: delayed 00:19:24.31594 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 009D39FB2F8 for ; Thu, 10 Aug 2006 20:13:08 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from EXCHANGEVS1.comtechtel.com (exchangevs1.comtechtel.com [65.170.132.200]) by svr4.postgresql.org (Postfix) with ESMTP id 8BF075AF03D for ; Thu, 10 Aug 2006 22:53:41 +0000 (GMT) Received: from 10.5.0.133 ([10.5.0.133]) by EXCHANGEVS1.comtechtel.com ([10.10.0.15]) with Microsoft Exchange Server HTTP-DAV ; Thu, 10 Aug 2006 22:55:18 +0000 User-Agent: Microsoft-Entourage/11.2.1.051004 Date: Thu, 10 Aug 2006 18:53:33 -0400 Subject: Breaking up a query From: Saad Anis To: Message-ID: Thread-Topic: Breaking up a query Thread-Index: Aca8z84tDOpRdijDEduuawARJEF3EA== Mime-version: 1.0 Content-type: multipart/mixed; boundary="B_3238080816_13574077" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200608/77 X-Sequence-Number: 26035 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3238080816_13574077 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit 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; --B_3238080816_13574077 Content-type: multipart/appledouble; boundary="B_3238080813_13602514" --B_3238080813_13602514 Content-type: application/applefile; name="ExplainAnalyzeOutput.txt" Content-transfer-encoding: base64 Content-disposition: attachment; filename="ExplainAnalyzeOutput.txt" AAUWBwACAAAAAAAAAAAAAAAAAAAAAAAAAAMAAAAJAAAAPgAAACAAAAADAAAAXgAAABgAAAAC AAAAdgAAAX5URVhUUipjaAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEV4cGxhaW5BbmFseXpl T3V0cHV0LnR4dAAAAQAAAAFMAAAATAAAADIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABI AAlNb25hY28AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAGAAQAQgAKAvADVQBCAAoC8ANV wQExDQAADQgAAA0IAAAAAAEAAAABAAAAAUwAAABMAAAAMgBOAIwJuQAAABwAMgAATVBTUgAA AAoD7f//AAAAAABOOuA= --B_3238080813_13602514 Content-type: text/plain; name="ExplainAnalyzeOutput.txt"; x-mac-creator="522A6368"; x-mac-type="54455854" Content-disposition: attachment; filename="ExplainAnalyzeOutput.txt" Content-transfer-encoding: base64 ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgIFFVRVJZIFBMQU4gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCiBTb3J0ICAo Y29zdD0xMTY1Ny44My4uMTE2NjAuMzMgcm93cz0xMDAwIHdpZHRoPTExMykgKGFjdHVhbCB0 aW1lPTczMTUuMzExLi43MzIzLjEyNCByb3dzPTE0ODcgbG9vcHM9MSkKICAgU29ydCBLZXk6 IHYueGN2cl9pZCwgdi5idW1wZXJfbnVtYmVyLCB2LnZlaGljbGVfdHlwZSwgaS4idmVyc2lv biIsIGkucmZpZF9zdGF0dXMsIHAuZXBvY2gKICAgLT4gIE1lcmdlIExlZnQgSm9pbiAgKGNv c3Q9MTE1ODguMDAuLjExNjA4LjAwIHJvd3M9MTAwMCB3aWR0aD0xMTMpIChhY3R1YWwgdGlt ZT03Mjc1Ljg1My4uNzMwMi45NzMgcm93cz0xNDg3IGxvb3BzPTEpCiAgICAgICAgIE1lcmdl IENvbmQ6ICgib3V0ZXIiLmlkID0gImlubmVyIi5pbnRlcnJvZ2F0aW9uX2lkKQogICAgICAg ICAtPiAgU29ydCAgKGNvc3Q9MTE1MTguMTcuLjExNTIwLjY3IHJvd3M9MTAwMCB3aWR0aD0x MDkpIChhY3R1YWwgdGltZT04NzguNTY2Li44NzguODU4IHJvd3M9NTUgbG9vcHM9MSkKICAg ICAgICAgICAgICAgU29ydCBLZXk6IGkuaWQKICAgICAgICAgICAgICAgLT4gIEhhc2ggTGVm dCBKb2luICAoY29zdD0xMTQwOC44My4uMTE0NjguMzQgcm93cz0xMDAwIHdpZHRoPTEwOSkg KGFjdHVhbCB0aW1lPTg3Ni4zNjkuLjg3OC4yMDggcm93cz01NSBsb29wcz0xKQogICAgICAg ICAgICAgICAgICAgICBIYXNoIENvbmQ6ICgoIm91dGVyIi5pZCA9ICJpbm5lciIucG9zaXRp b25faWQpIEFORCAoIm91dGVyIi5pZCA9ICJpbm5lciIudmVoaWNsZV9pZCkpCiAgICAgICAg ICAgICAgICAgICAgIC0+ICBNZXJnZSBKb2luICAoY29zdD0xMTM4My44My4uMTE0MzMuMzMg cm93cz0xMDAwIHdpZHRoPTEwMSkgKGFjdHVhbCB0aW1lPTcwNy4xOTAuLjcwOC4yOTIgcm93 cz01NSBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgICAgICAgICBNZXJnZSBDb25kOiAo Im91dGVyIi5pZCA9ICJpbm5lciIudmVoaWNsZV9pZCkKICAgICAgICAgICAgICAgICAgICAg ICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgdmVoaWNsZXNfcGtleSBvbiB2ZWhpY2xlcyB2 ICAoY29zdD0wLjAwLi4zMi4wMCByb3dzPTEwMDAgd2lkdGg9NzcpIChhY3R1YWwgdGltZT0w LjAyMy4uMC4wOTMgcm93cz0xMCBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgICAgICAg ICAtPiAgU29ydCAgKGNvc3Q9MTEzODMuODMuLjExMzg2LjMzIHJvd3M9MTAwMCB3aWR0aD0y OCkgKGFjdHVhbCB0aW1lPTcwNy4xMzQuLjcwNy40MjMgcm93cz01NSBsb29wcz0xKQogICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICBTb3J0IEtleTogcC52ZWhpY2xlX2lkCiAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBNZXJnZSBMZWZ0IEpvaW4gIChj b3N0PTAuMDAuLjExMzM0LjAwIHJvd3M9MTAwMCB3aWR0aD0yOCkgKGFjdHVhbCB0aW1lPTcw NS4xMDQuLjcwNi43ODkgcm93cz01NSBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICBNZXJnZSBDb25kOiAoIm91dGVyIi5pZCA9ICJpbm5lciIucG9z aXRpb25faWQpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEZpbHRl cjogKE5PVCAoc3VicGxhbikpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIHBvc2l0aW9uc19wa2V5IG9uIHBvc2l0aW9ucyBw ICAoY29zdD0wLjAwLi4zMi4wMCByb3dzPTEwMDAgd2lkdGg9MjgpIChhY3R1YWwgdGltZT0w LjAxOS4uOTAuOTIwIHJvd3M9MTM5NTggbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgZGF0YV90cmFuc2ZlcnNf cG9zaXRpb25faWRfaWR4IG9uIGRhdGFfdHJhbnNmZXJzIGR0ICAoY29zdD0wLjAwLi4zMi4w MCByb3dzPTEwMDAgd2lkdGg9OCkgKGFjdHVhbCB0aW1lPTAuMDE1Li45MS44NTkgcm93cz0x MzkwMyBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBT dWJQbGFuCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIFJl c3VsdCAgKGNvc3Q9MC4wMC4uMjAuMDAgcm93cz0xMDAwIHdpZHRoPTApIChhY3R1YWwgdGlt ZT0wLjAxNy4uMC4wMTcgcm93cz0xIGxvb3BzPTEzOTU4KQogICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgIE9uZS1UaW1lIEZpbHRlcjogKCQxID0gMSkK ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgU2Vx IFNjYW4gb24gZGF0YV90cmFuc2ZlcnMgIChjb3N0PTAuMDAuLjIwLjAwIHJvd3M9MTAwMCB3 aWR0aD0wKSAoYWN0dWFsIHRpbWU9MC4wMDYuLjAuMDA2IHJvd3M9MSBsb29wcz0xMzkwMykK ICAgICAgICAgICAgICAgICAgICAgLT4gIEhhc2ggIChjb3N0PTIwLjAwLi4yMC4wMCByb3dz PTEwMDAgd2lkdGg9MjQpIChhY3R1YWwgdGltZT0xNjguMzE3Li4xNjguMzE3IHJvd3M9MCBs b29wcz0xKQogICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4gb24gaW50 ZXJyb2dhdGlvbnMgaSAgKGNvc3Q9MC4wMC4uMjAuMDAgcm93cz0xMDAwIHdpZHRoPTI0KSAo YWN0dWFsIHRpbWU9MC4wMjEuLjg1LjM4OCByb3dzPTEzOTU4IGxvb3BzPTEpCiAgICAgICAg IC0+ICBTb3J0ICAoY29zdD02OS44My4uNzIuMzMgcm93cz0xMDAwIHdpZHRoPTEyKSAoYWN0 dWFsIHRpbWU9MzMzNC4yMzAuLjQ4MjguODU0IHJvd3M9Mjg0NDcxIGxvb3BzPTEpCiAgICAg ICAgICAgICAgIFNvcnQgS2V5OiB0LmludGVycm9nYXRpb25faWQKICAgICAgICAgICAgICAg LT4gIFNlcSBTY2FuIG9uIHRhZ3MgdCAgKGNvc3Q9MC4wMC4uMjAuMDAgcm93cz0xMDAwIHdp ZHRoPTEyKSAoYWN0dWFsIHRpbWU9MC4wMDkuLjE1ODcuNjYzIHJvd3M9Mjg0NDcxIGxvb3Bz PTEpCiBUb3RhbCBydW50aW1lOiA3MzU1LjI1NCBtcwooMjggcm93cykK --B_3238080813_13602514-- --B_3238080816_13574077--