Re: Massive performance issues

On Thu, Sep 01, 2005 at 02:47:06PM -0400, Tom Lane wrote:
> Matthew Sackman <matthew(at)lshift(dot)net> writes:
> > Obviously, to me, this is a problem, I need these queries to be under a
> > second to complete. Is this unreasonable?
>
> Yes. Pulling twenty thousand rows at random from a table isn't free.

I appreciate that. But I'm surprised by how un-free it seems to be.
And it seems others here have performance I need on similar hardware.

> You were pretty vague about your disk hardware, which makes me think
> you didn't spend a lot of money on it ... and on low-ball hardware,
> that sort of random access speed just isn't gonna happen.

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!

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.

> If the queries you need are very consistent, you might be able to get
> some mileage out of CLUSTERing by the relevant index ... but the number
> of indexes you've created makes me think that's not so ...

No, the queries, whilst in just three distinct forms, will effectively
be for fairly random values.

Matthew