Re: Statistics collection question

From: Alban Hertroys <alban(at)magproductions(dot)nl>
To: Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Statistics collection question
Date: 2007-09-03 15:40:41
Message-ID: 46DC2AF9.3040905@magproductions.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Phoenix Kiula wrote:
> Lots of posts here in reponse to performance question have the
> recommendation "increase the stats on that column". From whatever
> succint reading is made available on the postgres site, I gather that
> this aids the planner in getting some info about some of the data. Am
> I missing something here, or totally off-base?

As I understand it it's a sample of how the data is distributed.
Probably it's based on statistical mathematics that specifies a minimum
size for a representive sample of a given data set. It boils down to:
"If you want to know how many people like vanilla ice cream, how many
people do you need to ask their preference?".

> The issue is that I don't quite get why MySQL can fetch one indexed
> row (i.e., SQL that ends with a very simple "WHERE indexed_column =
> 'constant' ") in a matter of milliseconds, but PgSQL is taking 5 to 6
> seconds on an average at least for the first time. I use RAPTOR 15K
> drives, they're not SCSI but they're not exactly "cheap disks" either.
> And I have 4GB RAM. The explain select shows that index is being
> used!

That's definitely not normal. I have a smallish table here containing
2.5 million records, and querying for one with a specific index takes
141 micro(!) seconds. The hardware involved is a dual opteron with 4G,
in a xen domain; I don't know what disks are used, but I doubt they're
raptors.

So something is wrong with your setup, that much is obvious. I sincerely
doubt that postgres is to blame here.

You did check that you're not connecting through the internet and
getting a DNS timeout?

Regards,
--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Phoenix Kiula 2007-09-03 16:05:07 Re: Statistics collection question
Previous Message Alvaro Herrera 2007-09-03 15:34:13 Re: Statistics collection question