On Wed, Sep 22, 2010 at 11:18 AM, Ozer, Pam <pozer@automotive.com> wrote:
> 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.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company