pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Arjen van der Meijden <acmmailing@tweakers.net>
To: Matthew Sackman <matthew@lshift.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Massive performance issues
Date: Thu, 01 Sep 2005 22:54:45 +0200
Message-ID: <43176A95.70506@tweakers.net> (raw)
In-Reply-To: <20050901174231.GE23970@pongo.lshift.net>
References: <20050901174231.GE23970@pongo.lshift.net>

On 1-9-2005 19:42, Matthew Sackman wrote:
> Obviously, to me, this is a problem, I need these queries to be under a
> second to complete. Is this unreasonable? What can I do to make this "go
> faster"? I've considered normalising the table but I can't work out
> whether the slowness is in dereferencing the pointers from the index
> into the table or in scanning the index in the first place. And
> normalising the table is going to cause much pain when inserting values
> and I'm not entirely sure if I see why normalising it should cause a
> massive performance improvement.

In this case, I think normalising will give a major decrease in on-disk 
table-size of this large table and the indexes you have. If that's the 
case, that in itself will speed-up all i/o-bound queries quite a bit.

locality_1, _2, city and county can probably be normalised away without 
much problem, but going from varchar's to integers will probably safe 
you quite a bit of (disk)space.

But since it won't change the selectivity of indexes, so you won't get 
more index-scans instead of sequential scans, I suppose.
I think its not that hard to create a normalized set of tables from this 
  data-set (using insert into tablename select distinct ... from address 
and such, insert into address_new (..., city) select ... (select cityid 
from cities where city = address.city) from address)
So its at least relatively easy to figure out the performance 
improvement from normalizing the dataset a bit.

If you want to improve your hardware, have a look at the Western Digital 
Raptor-series SATA disks, they are fast scsi-like SATA drives. You may 
also have a look at the amount of memory available, to allow caching 
this (entire) table.

Best regards,

Arjen



view thread (33+ messages)  latest in thread

Message-ID: <43176A95.70506@tweakers.net>
Permalink:  ../43176A95.70506@tweakers.net/
Also on:    postgresql.org/message-id/43176A95.70506@tweakers.net

 · 

reply
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: acmmailing@tweakers.net, matthew@lshift.net
  Subject: Re: Massive performance issues
  In-Reply-To: <43176A95.70506@tweakers.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