From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: pgsql-performance@postgresql.org
Subject: Re: Massive performance issues
Date: Fri, 2 Sep 2005 09:50:01 -0700
User-Agent: KMail/1.8
Cc: Matthew Sackman <matthew@lshift.net>
References: <20050901174231.GE23970@pongo.lshift.net>
	<5178.1125600426@sss.pgh.pa.us>
	<20050901190808.GB7131@pongo.lshift.net>
In-Reply-To: <20050901190808.GB7131@pongo.lshift.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200509020950.01600.josh@agliodbs.com>

Matthew,

> Well, this is a development box. But the live box wouldn't be much more
> than RAID 1 on SCSI 10ks so that should only be a halving of seek time,
> not the 1000 times reduction I'm after!

If you're looking for 1000 times reduction, I think you're going to need=20
*considerably* beefier hardware.   You'd pretty much have to count on the=20
whole DB being in RAM, and a CPU being always available for incoming querie=
s.

> In fact, now I think about it, I have been testing on a 2.4 kernel on a
> dual HT 3GHz Xeon with SCSI RAID array and the performance is only
> marginally better.

Yes, but HT sucks for databases, so you're probably bottlenecking yourself =
on=20
CPU on that machine.  =20

However, if this is the query you really want to optimize for:

select street, locality_1, locality_2, city from address=20
where (city =3D 'Nottingham' or locality_2 =3D 'Nottingham'
=A0 =A0 =A0 =A0or locality_1 =3D 'Nottingham')
=A0 and upper(substring(street from 1 for 1)) =3D 'A'=20
group by street, locality_1, locality_2, city
order by street
limit 20 offset 0

=2E.. then this is the query you should test on.   Although I will say that=
 your=20
denormalized schema is actually hurting you siginificantly with the above=20
type of query; indexes aren't going to be possible for it.

=2D-=20
Josh Berkus
Aglio Database Solutions
San Francisco