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=HnlxUIzfMT2Vcn05po7/xpwOnR5y05xehONlNhIeepeC0H0UIKx4Up74AsTKkVXJavikWCGyvOzDHeJg4Cn/WKsrOCNTy0yD0PXW3XgZuJMwzn6V2zyyBejuuHLc+JldRGkgJbOdvdBt8FMJXrL8YoP2rBLdL6CTRy1An2DgrhU=
Message-ID: <b42b73150610042326v7fdeed17rf6b96e52a681a6db@mail.gmail.com>
Date: Thu, 5 Oct 2006 11:56:23 +0530
From: "Merlin Moncure" <mmoncure@gmail.com>
To: "Carlo Stonebanks" <stonec.register@sympatico.ca>
Subject: Re: Performance Optimization for Dummies 2 - the SQL
Cc: pgsql-performance@postgresql.org
In-Reply-To: <eg1dai$1tfa$1@news.hub.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <eft782$5a1$1@news.hub.org>
	<b42b73150610030804q2719222du57c63a085e26a5a4@mail.gmail.com>
	<efu8tf$1blk$1@news.hub.org>
	<b42b73150610031221y4955e663ped999a83a810d318@mail.gmail.com>
	<eg0u6e$hbr$1@news.hub.org>
	<b42b73150610041407y3554f311u1329c4f3bdc53999@mail.gmail.com>
	<eg1dai$1tfa$1@news.hub.org>

On 10/5/06, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> Hi Merlin,
>
> Here are the results. The query returned more rows (65 vs 12) because of the
> vague postal_code.

right.  interestingly, the index didn't work properly anyways.
regardless, this is easily solvable but it looks like we might be
looking in the wrong place.  do we have an multi-column index on
facility_address(facility_id, address_id)?  did you run analyze?

> In reality, we would have to modify the postal_code logic to take advantage
> of full zip codes when they were avalable, not unconditionally truncate
> them.
>
> Carlo
>
> 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 as f
>    join mdx_core.facility_address as fa
>       on fa.facility_id = f.facility_id
>    join mdx_core.address as a
>       on a.address_id = fa.address_id
>    where
>       (a.country_code, a.state_code, mdx_core.zip_trunc(a.postal_code)) =
> ('US', 'IL', mdx_core.zip_trunc('60640-5759'))
>    order by facility_id
>
> "Sort  (cost=6474.78..6474.84 rows=25 width=103) (actual
> time=217.279..217.311 rows=65 loops=1)"
> "  Sort Key: f.facility_id"
> "  ->  Nested Loop  (cost=2728.54..6474.20 rows=25 width=103) (actual
> time=35.828..217.059 rows=65 loops=1)"
> "        ->  Hash Join  (cost=2728.54..6384.81 rows=25 width=72) (actual
> time=35.801..216.117 rows=65 loops=1)"
> "              Hash Cond: ("outer".address_id = "inner".address_id)"
> "              ->  Seq Scan on facility_address fa  (cost=0.00..3014.68
> rows=128268 width=12) (actual time=0.007..99.072 rows=128268 loops=1)"
> "              ->  Hash  (cost=2728.50..2728.50 rows=19 width=64) (actual
> time=33.618..33.618 rows=39 loops=1)"
> "                    ->  Bitmap Heap Scan on address a  (cost=48.07..2728.50
> rows=19 width=64) (actual time=2.569..33.491 rows=39 loops=1)"
> "                          Recheck Cond: ((country_code = 'US'::bpchar) AND
> ((state_code)::text = 'IL'::text))"
> "                          Filter: (mdx_core.zip_trunc(postal_code) =
> '60640'::text)"
> "                          ->  Bitmap Index Scan on
> address_country_state_zip_trunc_idx  (cost=0.00..48.07 rows=3846 width=0)
> (actual time=1.783..1.783 rows=3554 loops=1)"
> "                                Index Cond: ((country_code = 'US'::bpchar)
> AND ((state_code)::text = 'IL'::text))"
> "        ->  Index Scan using facility_pkey on facility f  (cost=0.00..3.56
> rows=1 width=35) (actual time=0.009..0.010 rows=1 loops=65)"
> "              Index Cond: ("outer".facility_id = f.facility_id)"
> "Total runtime: 217.520 ms"