From: mark <dvlhntr@gmail.com>
To: 'Ozer, Pam' <pozer@automotive.com>
To: 'Robert Haas' <robertmhaas@gmail.com>
Cc: 'Craig James' <craig_james@emolecules.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Using Between
Date: Wed, 22 Sep 2010 18:51:23 -0600
Message-ID: <004301cb5ab9$733565c0$59a03140$@com> (raw)
In-Reply-To: <216FFB77CBFAEE4B8EE4DF0A939FF1D10182A4@mail-001.corp.automotive.com>
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>
>>> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dvlhntr@gmail.com, pozer@automotive.com, robertmhaas@gmail.com, craig_james@emolecules.com
Subject: Re: Using Between
In-Reply-To: <004301cb5ab9$733565c0$59a03140$@com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox