X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 06A58D1BB71 for ; Wed, 17 Dec 2003 15:26:58 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 39356-07 for ; Wed, 17 Dec 2003 11:26:30 -0400 (AST) Received: from shire.ontko.com (shire.ontko.com [199.164.165.1]) by svr1.postgresql.org (Postfix) with ESMTP id E7190D1B4BB for ; Wed, 17 Dec 2003 11:26:25 -0400 (AST) Received: from nick (bilbo.ontko.com [199.164.165.101]) by shire.ontko.com (8.12.3/8.12.3/Debian-6.6) with SMTP id hBHFQ65l020851; Wed, 17 Dec 2003 10:26:15 -0500 Reply-To: From: "Nick Fankhauser" To: "Richard Poole" , "Pgsql-Performance@Postgresql. Org" Subject: Re: Nested loop performance Date: Wed, 17 Dec 2003 10:26:25 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) In-Reply-To: <20031216235541.GB8054@guests.deus.net> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/256 X-Sequence-Number: 5116 > It seems that your basic problem is that you're fetching lots of rows > from two big ol' tables. > It doesn't seem to me that there would be a substantially better plan > for this query with your tables as they stand. That's more or less the conclusion I had come to. I was just hoping someone else could point out an approach I've been missing. (sigh!) > If your data were more > normalised, then your big scans might be quicker (because their rows > would be smaller so they would hit fewer disk pages), This started off as a 5-table join on well-normalized data. Unfortunately, the actor table doesn't get any smaller, and the work involved in calculating the "case_count" information on the fly was clearly becoming a problem- particularly with actors that had a heavy caseload. (Busy attorneys and judges.) The actor_summary approach makes these previous problem cases go away, but the payback is that (as you correctly pointed out) queries on average citizens who only have one case suffer from the de-normalized approach. We're currently considering the approach of just returning all of the rows to our application, and doing the aggregation and limit work in the app. The inconsistency of the data makes it very tough for the query planner to come up with an strategy that is always a winner. Thanks for your thoughts! -Nick