X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C671D56649 for ; Thu, 3 Mar 2005 05:05:12 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22904-02 for ; Thu, 3 Mar 2005 05:05:08 +0000 (GMT) Received: from ct.radiology.uiowa.edu (ct.radiology.uiowa.edu [129.255.60.186]) by svr1.postgresql.org (Postfix) with ESMTP id C21FB5641B for ; Thu, 3 Mar 2005 05:05:06 +0000 (GMT) Received: from [192.168.1.11] (12-217-241-0.client.mchsi.com [12.217.241.0]) by ct.radiology.uiowa.edu (8.11.6/8.11.6) with ESMTP id j23554326883; Wed, 2 Mar 2005 23:05:04 -0600 Message-ID: <42269AFB.9010700@arbash-meinel.com> Date: Wed, 02 Mar 2005 23:04:59 -0600 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Ken Egervari Cc: pgsql-performance@postgresql.org Subject: Re: Help with tuning this query (more musings) References: <001001c51ef4$3849eeb0$6a01a8c0@KEN> <1109787227.31084.12.camel@localhost.localdomain> <000901c51f55$aa4b7400$6a01a8c0@KEN> <1109789394.31084.20.camel@localhost.localdomain> <000601c51f63$63c8bb10$6a01a8c0@KEN> <13490.1109795370@sss.pgh.pa.us> <000801c51f67$c3abdcc0$6a01a8c0@KEN> <14444.1109802588@sss.pgh.pa.us> <001a01c51f8f$32bba5b0$6a01a8c0@KEN> <000401c51f9b$219ebbd0$6a01a8c0@KEN> <001001c51f9b$f606cd40$6a01a8c0@KEN> In-Reply-To: <001001c51f9b$f606cd40$6a01a8c0@KEN> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig5E8BBD2D38AF6FF290F40351" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/46 X-Sequence-Number: 10751 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig5E8BBD2D38AF6FF290F40351 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Ken Egervari wrote: >> I took John's advice and tried to work with sub-selects. I tried >> this variation, which actually seems like it would make a difference >> conceptually since it drives on the person table quickly. But to my >> surprise, the query runs at about 375 milliseconds. I think it's >> because it's going over that shipment table multiple times, which is >> where the results are coming from. > > > I also made a version that runs over shipment a single time, but it's > exactly 250 milliseconds. I guess the planner does the exact same thing. > Why are you now left joining driver and carrier code, but inner joining shipment_status? I assume this is the *real* query that you are executing. From the earlier explain analyze, and your statements, the initial person p should be the heavily selective portion. And what does "driver" get you? It isn't in the return, and it isn't part of a selectivity clause. You are also double joining against carrier code, once as a left outer join, and once in the inner join. This query doesn't seem quite right. Are you sure it is generating the rows you are expecting? > select s.*, ss.* > > from shipment s > inner join shipment_status ss on s.current_status_id=ss.id > inner join release_code rc on ss.release_code_id=rc.id > left outer join driver d on s.driver_id=d.id > left outer join carrier_code cc on s.carrier_code_id=cc.id > where s.carrier_code_id in ( > select cc.id > from person p > inner join carrier_to_person ctp on p.id=ctp.person_id > inner join carrier c on ctp.carrier_id=c.id > inner join carrier_code cc on cc.carrier_id = c.id > where p.id = 355 > ) > and s.current_status_id is not null > and s.is_purged=false > and(rc.number='9' ) > and(ss.date>=current_date-31 ) > > order by ss.date desc You might want to post the explain analyze of this query to have a point of reference, but what about something like this: select s.*, ss.* from shipment_status ss on s.current_status_id=ss.id join (select s.* from shipment s where s.carrier_code_id in (select cc.id from person p inner join carrier_to_person ctp on p.id=ctp.person_id inner join carrier c on ctp.carrier_id=c.id inner join carrier_code cc on cc.carrier_id = c.id where p.id = 355 ) and s.current_status_id is not null and s.is_purged=false ) as i -- Just a name for the subselect since it is in a join inner join release_code rc on ss.release_code_id=rc.id where (rc.number='9' ) and(ss.date between current_date-31 and current_date()) order by ss.date desc My idea with this query is to minimize the number of shipment rows that need to be generated before joining with the other rows. My syntax is probably a little bit off, since I can't actually run it against real tables. But looking at your *original* query, you were getting 15000 rows out of shipment_status, and then 27700 rows out of shipment, which was then being merge-joined down to only 300 rows, and then hash-joined down to 39. I'm just trying to think of ways to prevent it from blossoming into 27k rows to start with. Please double check your query, because it seems to be grabbing unnecessary rows with the left joins, and then post another explain analyze with one (or several) different subselect forms. John =:-> --------------enig5E8BBD2D38AF6FF290F40351 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.6 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD4DBQFCJpr7JdeBCYSNAAMRArL1AJjJLQDR/C2yQ6j5jO56U1Zvf55xAKCpgtKD 89erOsVUz1I/Gl2TtXQ0jg== =AiOK -----END PGP SIGNATURE----- --------------enig5E8BBD2D38AF6FF290F40351--