> Table "public.address"
> Column | Type | Modifiers
> ----------------------+------------------------+-----------
> postcode_top | character varying(2) | not null
> postcode_middle | character varying(4) | not null
> postcode_bottom | character varying(7) | not null
consider making above fields char(x) not varchar(x) for small but
important savings.
> postcode | character varying(10) | not null
> property_type | character varying(15) | not null
> sale_type | character varying(10) | not null
> flat_extra | character varying(100) | not null
> number | character varying(100) | not null
> street | character varying(100) | not null
> locality_1 | character varying(100) | not null
> locality_2 | character varying(100) | not null
> city | character varying(100) | not null
> county | character varying(100) | not null
> Indexes:
> "address_city_index" btree (city)
> "address_county_index" btree (county)
> "address_locality_1_index" btree (locality_1)
> "address_locality_2_index" btree (locality_2)
> "address_pc_bottom_index" btree (postcode_bottom)
> "address_pc_middle_index" btree (postcode_middle)
> "address_pc_top_index" btree (postcode_top)
> "address_pc_top_middle_bottom_index" btree (postcode_top,
> postcode_middle, postcode_bottom)
> "address_pc_top_middle_index" btree (postcode_top,
postcode_middle)
> "address_postcode_index" btree (postcode)
> "address_property_type_index" btree (property_type)
> "address_street_index" btree (street)
> "street_prefix" btree (lower("substring"((street)::text, 1, 1)))
>
> Obviously, to me, this is a problem, I need these queries to be under
a
> second to complete. Is this unreasonable? What can I do to make this
"go
> faster"? I've considered normalising the table but I can't work out
> whether the slowness is in dereferencing the pointers from the index
> into the table or in scanning the index in the first place. And
> normalising the table is going to cause much pain when inserting
values
> and I'm not entirely sure if I see why normalising it should cause a
> massive performance improvement.
http://www.dbdebunk.com :)
> I need to get to the stage where I can run queries such as:
> 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
>
> and have the results very quickly.
>
> Any help most gratefully received (even if it's to say that I should
be
> posting to a different mailing list!).
this is correct list. did you run vacuum/analyze, etc?
Please post vacuum analyze times.
Merlin
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
