I’ve been scratching my head on this one for far too long. I have a query under Postgresql which retrieves the distance between too points given the knowledge of their zipcodes. This work because I have an incomplete table of mileages between arbitrary three digit zipcode pairs. Each time I use this table my queries take a long time and I could never understand why. It has to do with the type that postgresql assigns to computed text fields. I was doing something like this:<\/p>\n
The issue here is the type<\/em> of the expression: I’ve been scratching my head on this one for far too long. I have a query under Postgresql which retrieves the distance between too points given the knowledge of their zipcodes. This work because I have an incomplete table of mileages between arbitrary three digit zipcode pairs. Each time I use this table my queries … <\/p>\nSELECT * FROM worklist INNER JOIN partial_zipcode_mileage ON SUBSTRING(worklist.origin_zipcode, 1, 3) = partial_zipcode_mileage.origin_partial_zipcode...<\/code><\/p>\nSUBSTRING(worklist.origin_zipcode, 1, 3)<\/code> as compared to the type of the field partial_zipcode_mileage.origin_partial_zipcode<\/code>. The latter is a SQL CHAR(3) since it will always hold 3 characters. Postgresql assignes the first expression a type of TEXT since it has know way to know how bit a field you actually want. This prevents the postgresql engine from using the index and this, my query takes along time. Substitute SUBSTRING(worklist.origin_zipcode, 1, 3)::char(3)<\/code> in the statement and all is happy.<\/p>\n","protected":false},"excerpt":{"rendered":"