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 B3DD952A5A for ; Wed, 2 Mar 2005 18:56:56 +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 59192-07 for ; Wed, 2 Mar 2005 18:56:52 +0000 (GMT) Received: from fep6.cogeco.net (smtp.cogeco.net [216.221.81.25]) by svr1.postgresql.org (Postfix) with ESMTP id 1F95D52A71 for ; Wed, 2 Mar 2005 18:56:51 +0000 (GMT) Received: from windsortransportationclub.com (d57-59-156.home.cgocable.net [24.57.59.156]) by fep6.cogeco.net (Postfix) with SMTP id EB815FC7 for ; Wed, 2 Mar 2005 13:56:51 -0500 (EST) Received: (qmail 17878 invoked from network); 2 Mar 2005 19:27:49 -0000 Received: from d36-80-110.home1.cgocable.net (HELO KEN) (24.36.80.110) by 0 with SMTP; 2 Mar 2005 19:27:49 -0000 Message-ID: <002801c51f59$9c043900$6a01a8c0@KEN> From: "Ken Egervari" To: References: <001001c51ef4$3849eeb0$6a01a8c0@KEN> <1109787227.31084.12.camel@localhost.localdomain> <000901c51f55$aa4b7400$6a01a8c0@KEN> <1109789394.31084.20.camel@localhost.localdomain> Subject: Re: Help with tuning this query Date: Wed, 2 Mar 2005 13:56:57 -0500 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.696 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL X-Spam-Level: * X-Archive-Number: 200503/35 X-Sequence-Number: 10740 >> left join is for eager loading so that I don't have to run a seperate >> query >> to fetch the children for each shipment. This really does improve >> performance because otherwise you'll have to make N+1 queries to the >> database, and that's just too much overhead. > > are you saying that you are actually doing a > select s.*,ss.* ... > ? Yes, this is how the SQL should be written. When I manually converted the query, I forgot to include this detail. In hibernate, you don't need to specifiy the ss.* because you are dealing with objects, so you just say shipment. The ss.* is indicated in the "fetch" part of the Hibernate query. That was my mistake. > it might help the planner estimate better the number of cs rows > affected. whether this improves performance depends on whether > the best plans are sensitive to this. This sounds like a good idea since cs rows are quite large. shipment and shipment_status are the largest tables in the database and they will grow very large over time.