From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Cc: Matthew Sackman <matthew@lshift.net>
Subject: Re: Massive performance issues
Date: Fri, 2 Sep 2005 09:50:01 -0700
Message-ID: <200509020950.01600.josh@agliodbs.com> (raw)
In-Reply-To: <20050901190808.GB7131@pongo.lshift.net>
References: <20050901174231.GE23970@pongo.lshift.net>
<5178.1125600426@sss.pgh.pa.us>
<20050901190808.GB7131@pongo.lshift.net>
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
*considerably* beefier hardware. You'd pretty much have to count on the
whole DB being in RAM, and a CPU being always available for incoming queries.
> 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
CPU on that machine.
However, if this is the query you really want to optimize for:
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
... then this is the query you should test on. Although I will say that your
denormalized schema is actually hurting you siginificantly with the above
type of query; indexes aren't going to be possible for it.
--
Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, matthew@lshift.net
Subject: Re: Massive performance issues
In-Reply-To: <200509020950.01600.josh@agliodbs.com>
* 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