From: Matthew Sackman <matthew@lshift.net>
To: pgsql-performance@postgresql.org
Subject: Re: Massive performance issues
Date: Tue, 6 Sep 2005 16:51:24 +0100
Message-ID: <20050906155124.GM382@pongo.lshift.net> (raw)
In-Reply-To: <20050901174231.GE23970@pongo.lshift.net>
References: <20050901174231.GE23970@pongo.lshift.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: matthew@lshift.net
Subject: Re: Massive performance issues
In-Reply-To: <20050906155124.GM382@pongo.lshift.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox