X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 31E2B9DC99F for ; Mon, 6 Mar 2006 22:10:17 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 51541-02 for ; Mon, 6 Mar 2006 22:10:20 -0400 (AST) X-Greylist: delayed 01:55:49.871687 by SQLgrey- Received: from oc.hemac.com.mx (unknown [201.144.70.52]) by postgresql.org (Postfix) with ESMTP id 3D9639DC9C4 for ; Mon, 6 Mar 2006 22:10:12 -0400 (AST) Received: from [192.168.0.62] by oc.hemac.com.mx (MDaemon.PRO.v8.0.2.R) with ESMTP id md50000033635.msg for ; Mon, 06 Mar 2006 20:11:48 -0600 Message-ID: <440CEBE3.6030004@gmail.com> Date: Mon, 06 Mar 2006 20:11:47 -0600 From: "i.v.r." User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Help understanding indexes, explain, and optimizing References: <440CD0BB.2020800@gmail.com> <440CD673.8080208@gmail.com> In-Reply-To: <440CD673.8080208@gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Authenticated-Sender: ivanvr@oc.hemac.com.mx X-Spam-Processed: hermes.oc.hemac.com.mx, Mon, 06 Mar 2006 20:11:48 -0600 (not processed: message from trusted or authenticated source) X-Return-Path: ivanvega@gmail.com X-MDaemon-Deliver-To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.97 required=5 tests=[AWL=-1.731, RCVD_IN_NJABL_DUL=1.713, RCVD_IN_SORBS_DUL=1.988] X-Spam-Score: 1.97 X-Spam-Level: * X-Archive-Number: 200603/52 X-Sequence-Number: 17489 Chris escribió: > Have you done an 'analyze' or 'vacuum analyze' over these tables? > > A left outer join gets *everything* from the second table: > > > LEFT OUTER JOIN groups ON groups.id = locations.group_id > > LEFT OUTER JOIN schools ON schools.location_id = locations.id > > So they will load everything from groups and schools. Maybe they > should be left join's not left outer joins? > > Yes, I did that. I tried your other suggestion and it did improve it by about 200ms. I also repurposed the query by selecting first from the groups table and joining with the locations and schools tables, and that made all the difference. Now it's down to 32ms. Yipee! Thanks! Ivan V.