Date: Tue, 6 Sep 2005 16:51:24 +0100
From: Matthew Sackman <matthew@lshift.net>
To: pgsql-performance@postgresql.org
Subject: Re: Massive performance issues
Message-ID: <20050906155124.GM382@pongo.lshift.net>
Mail-Followup-To: pgsql-performance@postgresql.org
References: <20050901174231.GE23970@pongo.lshift.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20050901174231.GE23970@pongo.lshift.net>
User-Agent: Mutt/1.5.10i

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