DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=from:to:cc:references:in-reply-to:subject:date:message-id
	:mime-version:content-type:content-transfer-encoding:x-mailer
	:thread-index:content-language:x-cr-hashedpuzzle:x-cr-puzzleid;
	b=ZOmM9P3U+O0CgLxk9TZR4hiMN7gQ+FDiRJ8Uli2/3VgNB81T0SXKJoByrCKP6B3/oc
	M9eBe0eEDZBjrBY2N14tWnnxzYE4CpPcpTe8c4AZOHCbD9MrXkOdalqRmhBp6jJ0GCa1
	SbgdRwYF/+9v8IUtohoCV89FqRIzrH+eWRI0s=
From: "mark" <dvlhntr@gmail.com>
To: "'Ozer, Pam'" <pozer@automotive.com>,
	"'Robert Haas'" <robertmhaas@gmail.com>
Cc: "'Craig James'" <craig_james@emolecules.com>,
	<pgsql-performance@postgresql.org>
References: 
 <C3CA20D47A2DF94FBF9CF0AE8D964CC4124399@mail-001.corp.automotive.com><4C785B51.1020105@emolecules.com><216FFB77CBFAEE4B8EE4DF0A939FF1D1018247@mail-001.corp.automotive.com><AANLkTinH9Ej827Tghhrif2sG7iJp4aNq8opTh6Lx56kG@mail.gmail.com><216FFB77CBFAEE4B8EE4DF0A939FF1D101829E@mail-001.corp.automotive.com><AANLkTikSX12WDnHHYHLtN5YUi=dQHiL+gY8nCW=mnOYA@mail.gmail.com><216FFB77CBFAEE4B8EE4DF0A939FF1D10182A0@mail-001.corp.automotive.com>
	<AANLkTinxLadXdj3=DMZy97dz1-8_di2=58GyVNzpGW+-@mail.gmail.com>
	<216FFB77CBFAEE4B8EE4DF0A939FF1D10182A4@mail-001.corp.automotive.com>
In-Reply-To: 
 <216FFB77CBFAEE4B8EE4DF0A939FF1D10182A4@mail-001.corp.automotive.com>
Subject: Re: Using Between
Date: Wed, 22 Sep 2010 18:51:23 -0600
Message-ID: <004301cb5ab9$733565c0$59a03140$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: ActacwayJzQDn0BlR9STmMGaMQbDYAAACb8wABD4nXA=
Content-Language: en-us

>>> The question is how can we make it faster.

>>If there's just one region ID for any given postal code, you might try
>>adding a column to vehicleused and storing the postal codes there.
>>You could possibly populate that column using a trigger; probably it
>>doesn't change unless the postalcode changes.  Then you could index
>>that column and query against it directly, rather than joining to
>>PostalCodeRegionCountyCity.  Short of that, I don't see any obvious
>>way to avoid reading most of the vehicleused table.  There may or may
>>not be an index that can speed that up slightly and of course you can
>>always throw hardware at the problem, but fundamentally reading half a
>>million or more rows isn't going to be instantaneous.

>>Incidentally, it would probably simplify things to store postal codes
>>in the same case throughout the system. If you can avoid the need to
>>write lower(x) = lower(y) and just write x = y you may get better
>>plans.  I'm not sure that's the case in this particular example but
>>it's something to think about.

Something else you might test is bumping the read-ahead value. Most linux
installs have this at 256, might try bumping the value to ~8Meg and tune
from there . this may help you slightly for seq scan performance. As always:
YMMV. It's not going to magically fix low performing I/O subsystems and it
won't help many applications of PG but there are a few outlying instances
where this change can help a little bit. 


I am sure someone will step in and tell you it is a bad idea - AND they will
probably have perfectly valid reasons for why it is, so you will need to
consider the ramifications.. if at all possible test and tune to see. 
..: Mark


>>-- 
>>Robert Haas
>>EnterpriseDB: http://www.enterprisedb.com
>>The Enterprise Postgres Company

-- 
>>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>>To make changes to your subscription:
>>http://www.postgresql.org/mailpref/pgsql-performance