It would be good to see EXPLAIN ANALYZE output for the three queries
below (the real vs. estimated row counts being of interest).
The number of pages in your address table might be interesting to know too.
regards
Mark
Matthew Sackman wrote (with a fair bit of snippage):
explain select locality_2 from address where locality_2 = 'Manchester';
gives
QUERY PLAN
----------------------------------------------------------------
Seq Scan on address (cost=0.00..80677.16 rows=27923 width=12)
Filter: ((locality_2)::text = 'Manchester'::text)
explain select locality_1 from address where locality_1 = 'Manchester';
gives
QUERY PLAN
----------------------------------------------------------------
Index Scan using address_locality_1_index on address
(cost=0.00..69882.18 rows=17708 width=13)
Index Cond: ((locality_1)::text = 'Manchester'::text)
>
select street, locality_1, locality_2, city from address
where (city = 'Nottingham' or locality_2 = 'Nottingham'
or locality_1 = 'Nottingham')
and upper(substring(street from 1 for 1)) = 'A'
group by street, locality_1, locality_2, city
order by street
limit 20 offset 0
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
