Thank you. I will take a look at those suggestions. -----Original Message----- From: Robert Haas [mailto:robertmhaas@xxxxxxxxx] Sent: Wednesday, September 22, 2010 9:27 AM To: Ozer, Pam Cc: Craig James; pgsql-performance@xxxxxxxxxxxxxx Subject: Re: Using Between On Wed, Sep 22, 2010 at 11:18 AM, Ozer, Pam <pozer@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
