Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 613589FB222 for ; Thu, 5 Oct 2006 10:30:53 -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 37711-03 for ; Thu, 5 Oct 2006 13:30:49 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id ADE659F9F77 for ; Thu, 5 Oct 2006 10:30:48 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id 30so180138ugc for ; Thu, 05 Oct 2006 06:30:46 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=S3whRXDSSpz0xqe7KRM3gssSTQhGfperzwgbvsAo5qc/9IcScUYzLpgjRTTVubJSgFz3ArNJDTOUNouEEVb5fEcyAstln/gehiItHNY/p4C740cI8Bx5S8QobKNZUT8nooSoeWGlNZ4FPE95nTuNB44Vps4yD18tmlZL8BWBSyQ= Received: by 10.78.128.15 with SMTP id a15mr304627hud; Thu, 05 Oct 2006 06:30:45 -0700 (PDT) Received: by 10.78.35.18 with HTTP; Thu, 5 Oct 2006 06:30:45 -0700 (PDT) Message-ID: Date: Thu, 5 Oct 2006 09:30:45 -0400 From: "Merlin Moncure" To: "Carlo Stonebanks" Subject: Re: Performance Optimization for Dummies 2 - the SQL Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.199 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/126 X-Sequence-Number: 21255 On 10/5/06, Carlo Stonebanks wrote: > > do we have an multi-column index on > > facility_address(facility_id, address_id)? did you run analyze? > > There is an index on facility_address on facility_id. > > I didn't create an index on facility_address.address_id because I expected > joins to go in the other direction (from facility_address to address). > Nor did I create a multi-column index on facility_id, address_id because I > had yet to come up with a query that required that. right. well, since you are filtering on address, I would consider added an index on address_id or a multi column on address_id, facility_id (in addition to facility_id). also, I'd consider removing all the explicit joins like this: explain analyze select f.facility_id, fa.facility_address_id, a.address_id, f.facility_type_code, f.name, a.address, a.city, a.state_code, a.postal_code, a.country_code from mdx_core.facility f, mdx_core.facility_address fa, mdx_core.address a where fa.facility_id = f.facility_id and a.address_id = fa.address_id and a.country_code = 'US' and a.state_code = 'IL' and a.postal_code like '60640-5759'||'%' order by facility_id; yet another way to write that where clause is: (fa_address_id, fa.facility_id) = (a.address_id, f.facility_id) and a.country_code = 'US' and a.state_code = 'IL' and a.postal_code like '60640-5759'||'%' order by facility_id; I personally only use explicit joins when doing outer joins and even them push them out as far as possible. I like the row constructor style better because it shows the key relationships more clearly. I don't think it makes a difference in execution (go ahead and try it). If you do make a multi column key on facility_address, though, make sure to put they key fields in left to right order in the row constructor. Try adding a multi key on address_id and facility_id and run it this way. In a proper design you would have a primary key on these fields but with imported data you obviously have to make compromises :). > However, I still have a lot to learn about how SQL chooses its indexes, how > multi-column indexes are used, and when to use them (other than the > obvious - i.e. sort orders or relational expressions which request those > columns in one search expression) well, it's kind of black magic but if the database is properly laid out the function usually follows form pretty well. > Analyse is actually run every time a page of imported data loads into the > client program. This is currently set at 500 rows. ok. merlin