From: Craig Ringer <ringerc@ringerc.id.au>
To: Anibal David Acosta <aa@devshock.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: how fast index works?
Date: Wed, 07 Sep 2011 09:03:38 +0800
Message-ID: <4E66C2EA.2020109@ringerc.id.au> (raw)
In-Reply-To: <001501cc6cc3$3649a3c0$a2dceb40$@devshock.com>
References: <001501cc6cc3$3649a3c0$a2dceb40$@devshock.com>
On 7/09/2011 2:31 AM, Anibal David Acosta wrote:
>
> Hi everyone,
>
> My question is, if I have a table with 500,000 rows, and a SELECT of
> one row is returned in 10 milliseconds, if the table has 6,000,000 of
> rows and everything is OK (statistics, vacuum etc)
>
> can i suppose that elapsed time will be near to 10?
>
>
It's not that simple. In addition to the performance scaling Craig James
mentioned, there are cache effects.
Your 500,000 row index might fit entirely in RAM. This means that no
disk access is required to query and search it, making it extremely
fast. If the index on the larger table does NOT fit entirely in RAM, or
competes for cache space with other things so it isn't always cached in
RAM, then it might be vastly slower.
This is hard to test, because it's not easy to empty the caches. On
Linux you can the the VM's drop_caches feature, but that drops *all*
caches, including cached disk data from running programs, the PostgreSQL
system catalogs, etc. That makes it a rather unrealistic test when the
only thing you really want to remove from cache is your index and the
table associated with it.
The best way to test whether data of a certain size will perform well is
to create dummy data of that size and test with it. Anything else is
guesswork.
--
Craig Ringer
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: ringerc@ringerc.id.au, aa@devshock.com
Subject: Re: how fast index works?
In-Reply-To: <4E66C2EA.2020109@ringerc.id.au>
* 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