Hi,
Many thanks for all your thoughts and advice. With just 2GB or RAM, no
change to the harddisc (still SATA) but proper tuning of Postgresql
(still 7.4) and aggressive normalization to shrink row width, I have
managed to get suitable performance, with, when fully cached, queries on
a 5 million row data set, including queries such as:
select to_char(sale_date, 'DD Mon YYYY') as sale_date_text, cost,
property_types.type as property_type, sale_types.type as sale_type,
flat_extra, number, street, loc1.component as locality_1,
loc2.component as locality_2, city.component as city,
county.component as county, postcode
from address
inner join (
select id from address_components
where component = 'Woodborough'
) as t1
on locality_1_id = t1.id or locality_2_id = t1.id or city_id = t1.id
inner join (
select id, street from streets where street = 'Lowdham Lane'
) as t2
on street_id = t2.id
inner join sale_types
on sale_types.id = sale_type_id
inner join property_types
on property_types.id = property_type_id
inner join address_components as county
on county_id = county.id
inner join address_components as city
on city_id = city.id
inner join address_components as loc2
on locality_2_id = loc2.id
inner join address_components as loc1
on locality_1_id = loc1.id
order by sale_date desc limit 11 offset 0
completing within 50ms. I've also now managed to request that the full
production system will have 4GB of RAM (there are still a few queries
that don't quite fit in 2GB of RAM) and a 15kRPM SCSI HD.
So once again, thanks for all your help. I've literally been pulling my
hair out over this so it's great to have basically got it solved.
Matthew