pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Paul Thomas <paul@tmsl.demon.co.uk>
To: pgsql-performance @ postgresql . org <pgsql-performance@postgresql.org>
Subject: Re: statistics
Date: Thu, 8 Apr 2004 00:48:35 +0100
Message-ID: <20040408004835.A8476@bacon> (raw)
In-Reply-To: <BAY8-F42EA2VqFnW0kK0003930a@hotmail.com>
References: <BAY8-F42EA2VqFnW0kK0003930a@hotmail.com>


On 07/04/2004 22:05 Jaime Casanova wrote:
> What the statistics are? Where can i view it? where can i find info 
> about its field and why are they valuable information to performance?
> 
> thanx in advance,
> 
> Jaime Casanova


OK. An idiot's guide to statistics by a full-time idiot...

Let's start with a simple premise. I'm a RDBMS (forget that I'm actually 
an idiot for a moment...) and I've been asked for

select * from foo where bar = 7;

How do I go about fulfilling the reequest in the most efficient manner? 
(i.e., ASAP!)

One way might be to read through the whole table and return only those 
rows which match the where criteron - a sequential scan on the table.

But wait a minute, there is an index on column bar. Could I use this 
instead? Well, of course, I could use it but I have to keep sight of the 
goal of returning the data ASAP and I know that the act of reading 
index/reading table/... will have a performance penalty due to a lot more 
head movement on the disk. So how do I make chose between a sequential 
scan and an index scan? Let's lokk at a couple of extreme scenarios:

1) let's look at the condition where all or virtually all of the bar 
columns are populated wityh the value 7. In this case it would be more 
efficient to read sequentially through the table.

2) the opposite of (1) - very few of the bar columns have the value 7. In 
this case using the index could be a winner.

So generalising, I need to be able to estimate whether doing a sequential 
scan is more efficient that an index scan and this comes down to 2 factors:

a) the cost of moving the disk heads all over the place (random page cost)
b) the spread of values in the selecting column(s)

(a) is specfified in postgresql.conf (see archives for much discusion 
about what the value should be..)
(b) is determined by the dastardly trick of actually sampling the data in 
the table!!! That's what analyze does. It samples your table(s) and uses 
the result to feeede into it's descision about when to flip between 
sequential and index scans.

Hope this makes some kind of sense...

-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for 
Business             |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+



view thread (3+ messages)  latest in thread

Message-ID: <20040408004835.A8476@bacon>
Permalink:  ../20040408004835.A8476@bacon/
Also on:    postgresql.org/message-id/20040408004835.A8476@bacon

 · 

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: paul@tmsl.demon.co.uk
  Subject: Re: statistics
  In-Reply-To: <20040408004835.A8476@bacon>

* 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