pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (3+ messages)

Message-ID: <1057849750.38433.12.camel@freebsd47>
Permalink:  ../1057849750.38433.12.camel@freebsd47/
Also on:    postgresql.org/message-id/1057849750.38433.12.camel@freebsd47

 · 

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 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