Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 30677B5DBD1 for ; Tue, 6 Sep 2011 16:16:03 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 18948-08 for ; Tue, 6 Sep 2011 19:15:56 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gw0-f46.google.com (mail-gw0-f46.google.com [74.125.83.46]) by mail.postgresql.org (Postfix) with ESMTP id 3DC83B5DBD6 for ; Tue, 6 Sep 2011 16:15:55 -0300 (ADT) Received: by gwaa12 with SMTP id a12so3750875gwa.19 for ; Tue, 06 Sep 2011 12:15:55 -0700 (PDT) Received: by 10.42.151.4 with SMTP id c4mr2634175icw.123.1315336555309; Tue, 06 Sep 2011 12:15:55 -0700 (PDT) Received: from Craig-Jamess-MacBook-Pro.local (wsip-70-167-122-86.sd.sd.cox.net [70.167.122.86]) by mx.google.com with ESMTPS id j2sm2001642ibx.11.2011.09.06.12.15.53 (version=SSLv3 cipher=OTHER); Tue, 06 Sep 2011 12:15:54 -0700 (PDT) Message-ID: <4E6671DE.40506@emolecules.com> Date: Tue, 06 Sep 2011 12:17:50 -0700 From: Craig James User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US; rv:1.9.2.21) Gecko/20110830 Thunderbird/3.1.13 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: how fast index works? References: <001501cc6cc3$3649a3c0$a2dceb40$@devshock.com> In-Reply-To: <001501cc6cc3$3649a3c0$a2dceb40$@devshock.com> Content-Type: multipart/alternative; boundary="------------000003070207070000010202" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201109/67 X-Sequence-Number: 44843 This is a multi-part message in MIME format. --------------000003070207070000010202 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 9/6/11 11: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? > Theoretically the index is a B-tree with log(N) performance, so a larger table could be slower. But in a real database, the entire subtree might fall together in one spot on the disk, so retrieving a record from a 500,000 record database could take the same time as a 6,000,000 record database. On the other hand, if you do a lot of updates and don't have your autovacuum parameters set right, a 500,000 record index might get quite bloated and slow as it digs through several disk blocks to find one record. There is no simple answer to your question. In a well-maintained database, 6,000,000 records are not a problem. Craig --------------000003070207070000010202 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On 9/6/11 11: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?

Theoretically the index is a B-tree with log(N) performance, so a larger table could be slower. But in a real database, the entire subtree might fall together in one spot on the disk, so retrieving a record from a 500,000 record database could take the same time as a 6,000,000 record database.

On the other hand, if you do a lot of updates and don't have your autovacuum parameters set right, a 500,000 record index might get quite bloated and slow as it digs through several disk blocks to find one record.

There is no simple answer to your question. In a well-maintained database, 6,000,000 records are not a problem.

Craig
--------------000003070207070000010202--