From: Chris Bowlby <excalibur@hub.org>
To: Rod Taylor <rbt@rbt.ca>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Some very weird behaviour....
Date: 10 Jul 2003 12:09:10 -0300
Message-ID: <1057849750.38433.12.camel@freebsd47> (raw)
In-Reply-To: <1057772554.46100.103.camel@jester>
References: <1057771777.33712.98.camel@freebsd47>
<1057772554.46100.103.camel@jester>
On Wed, 2003-07-09 at 14:42, Rod Taylor wrote:
Clustering definatly helped with that case, and appears to have helped
with all of the dates I have had high execution times for... thanks for
the tip..
> > To give you some perspective on the size of the dataset and the
> > performance level we are hitting, here are some "good" results based on
> > some explains:
>
> Before Tom jumps in taking all the fun out of trying to solve it...
>
>
> The estimates in the slow queries seem perfectly reasonable. In fact,
> the cost estimates of both the slow and fast queries are the same which
> is what would be expected if all of the data was distributed evenly
> amongst the table.
>
> Given it's a date, I would guess that the data is generally inserted
> into the table in an order following the date but for some reason those
> 'high' dates have their data distributed more evenly amongst the table.
> Clustered data will have fewer disk seeks and deal with fewer pages of
> information in general which makes for a much faster query. Distributed
> data will have to pull out significantly more information from the disk,
> throwing most of it away.
>
> I would guess that sometime on 2002-05-25 someone did a bit of data
> cleaning (deleting records). Next day the free space map had entries
> available in various locations within the table, and used them rather
> than appending to the end. With 89 Million records with date being
> significant, I'm guessing there aren't very many modifications or
> deletes on it.
>
> So.. How to solve the problem? If this is the type of query that occurs
> most often, you do primarily inserts, and the inserts are generally
> created following date, cluster the table by index "some_table_ix_0".
> The clustering won't degrade very much since that is how you naturally
> insert the data.
--
Chris Bowlby <excalibur@hub.org>
Hub.Org Networking Services
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: excalibur@hub.org, rbt@rbt.ca
Subject: Re: Some very weird behaviour....
In-Reply-To: <1057849750.38433.12.camel@freebsd47>
References: <1057771777.33712.98.camel@freebsd47> <1057772554.46100.103.camel@jester> <1057849750.38433.12.camel@freebsd47>
* 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