Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 5DFE8B5DBC4 for ; Tue, 6 Sep 2011 17:28:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 69506-06 for ; Tue, 6 Sep 2011 20:27:51 +0000 (UTC) X-Greylist: delayed 01:48:47.003036 by SQLgrey-1.7.6 Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com [209.85.213.174]) by mail.postgresql.org (Postfix) with ESMTP id 4F265B5DBBE for ; Tue, 6 Sep 2011 17:27:51 -0300 (ADT) Received: by yxj19 with SMTP id 19so3291063yxj.19 for ; Tue, 06 Sep 2011 13:27:51 -0700 (PDT) Received: by 10.150.225.4 with SMTP id x4mr4408729ybg.106.1315340871069; Tue, 06 Sep 2011 13:27:51 -0700 (PDT) Received: from AnibalVAIO ([200.85.32.68]) by mx.google.com with ESMTPS id 6sm1381192anu.16.2011.09.06.13.27.46 (version=TLSv1/SSLv3 cipher=OTHER); Tue, 06 Sep 2011 13:27:47 -0700 (PDT) From: "Anibal David Acosta" To: "'Craig James'" , References: <001501cc6cc3$3649a3c0$a2dceb40$@devshock.com> <4E6671DE.40506@emolecules.com> In-Reply-To: <4E6671DE.40506@emolecules.com> Subject: Re: how fast index works? Date: Tue, 6 Sep 2011 16:28:12 -0400 Message-ID: <008b01cc6cd3$820581c0$86108540$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_008C_01CC6CB1.FAF51A40" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQGl5SRIHx/2OCDGLlzb3OCfbzCIuAKHy+WglXmeNUA= Content-Language: es-py 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/71 X-Sequence-Number: 44847 This is a multipart message in MIME format. ------=_NextPart_000_008C_01CC6CB1.FAF51A40 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Thanks! De: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] En nombre de Craig James Enviado el: martes, 06 de septiembre de 2011 03:18 p.m. Para: pgsql-performance@postgresql.org Asunto: Re: [PERFORM] how fast index works? 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 ------=_NextPart_000_008C_01CC6CB1.FAF51A40 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Thanks!

De: pgsql-performance-owner@postgresql.org = [mailto:pgsql-performance-owner@postgresql.org] En nombre de = Craig James
Enviado el: martes, 06 de septiembre de 2011 = 03:18 p.m.
Para: = pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] how = fast index works?

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

------=_NextPart_000_008C_01CC6CB1.FAF51A40--