From aa@devshock.com Tue Sep 6 18:58:59 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 352EAB5DBC4 for ; Tue, 6 Sep 2011 15:58:59 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 82532-07 for ; Tue, 6 Sep 2011 18:58:53 +0000 (UTC) X-Greylist: delayed 00:27:40.909807 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 E57CCB5DBBE for ; Tue, 6 Sep 2011 15:58:52 -0300 (ADT) Received: by gwaa12 with SMTP id a12so3737634gwa.19 for ; Tue, 06 Sep 2011 11:58:52 -0700 (PDT) Received: by 10.91.93.2 with SMTP id v2mr3939916agl.100.1315333871381; Tue, 06 Sep 2011 11:31:11 -0700 (PDT) Received: from AnibalVAIO ([200.85.32.68]) by mx.google.com with ESMTPS id c15sm1023206anm.3.2011.09.06.11.31.06 (version=TLSv1/SSLv3 cipher=OTHER); Tue, 06 Sep 2011 11:31:08 -0700 (PDT) From: "Anibal David Acosta" To: Subject: how fast index works? Date: Tue, 6 Sep 2011 14:31:33 -0400 Message-ID: <001501cc6cc3$3649a3c0$a2dceb40$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0016_01CC6CA1.AF38EE20" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AcxswSuliKz++YSlSPqvInr3MN13SQ== 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/64 X-Sequence-Number: 44840 This is a multipart message in MIME format. ------=_NextPart_000_0016_01CC6CA1.AF38EE20 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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? ------=_NextPart_000_0016_01CC6CA1.AF38EE20 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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?

 

 

 

 

------=_NextPart_000_0016_01CC6CA1.AF38EE20-- From craig_james@emolecules.com Tue Sep 6 19:16:03 2011 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-- From aa@devshock.com Tue Sep 6 20:28:02 2011 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-- From ringerc@ringerc.id.au Wed Sep 7 01:03:54 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id B1E88B5DC2E for ; Tue, 6 Sep 2011 22:03:54 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 97700-03 for ; Wed, 7 Sep 2011 01:03:48 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vx0-f174.google.com (mail-vx0-f174.google.com [209.85.220.174]) by mail.postgresql.org (Postfix) with ESMTP id 66A96B5DC0A for ; Tue, 6 Sep 2011 22:03:48 -0300 (ADT) Received: by vxi9 with SMTP id 9so339030vxi.19 for ; Tue, 06 Sep 2011 18:03:48 -0700 (PDT) Received: by 10.52.23.130 with SMTP id m2mr844176vdf.191.1315357428273; Tue, 06 Sep 2011 18:03:48 -0700 (PDT) Received: from [10.1.1.8] (124-169-102-162.dyn.iinet.net.au [124.169.102.162]) by mx.google.com with ESMTPS id q1sm1406564vds.4.2011.09.06.18.03.46 (version=SSLv3 cipher=OTHER); Tue, 06 Sep 2011 18:03:47 -0700 (PDT) Message-ID: <4E66C2EA.2020109@ringerc.id.au> Date: Wed, 07 Sep 2011 09:03:38 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:6.0.1) Gecko/20110830 Thunderbird/6.0.1 MIME-Version: 1.0 To: Anibal David Acosta CC: 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="------------050707010407090302000105" 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/73 X-Sequence-Number: 44849 This is a multi-part message in MIME format. --------------050707010407090302000105 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------050707010407090302000105 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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
--------------050707010407090302000105-- From mmoncure@gmail.com Thu Sep 8 17:36:05 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id F26A0B5DC39 for ; Thu, 8 Sep 2011 14:36:05 -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 76837-08 for ; Thu, 8 Sep 2011 17:35:58 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yw0-f46.google.com (mail-yw0-f46.google.com [209.85.213.46]) by mail.postgresql.org (Postfix) with ESMTP id AFF47B5DBE0 for ; Thu, 8 Sep 2011 14:35:58 -0300 (ADT) Received: by ywf7 with SMTP id 7so863865ywf.19 for ; Thu, 08 Sep 2011 10:35:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=/c2B9OdRDdoNevHLac2HJhM+mLW1AWi68QPR3QpY/3U=; b=PrAOepjihhyNbrTs1J/v18Rklg6wkhGfJlqS7inhgx1Eab9eVfse1r9H/ImVGE3hY3 XdL6fAzMC+tNV/4Fj2DDXdZyCUwVLpBrZQJPqqVIc5IkkKI5xm8AMMo/mAZTmNhcnyO3 TnyENeSjqOJbfQRk9tzAMGmWNbGM6JH/AJ8VA= MIME-Version: 1.0 Received: by 10.236.79.72 with SMTP id h48mr6429163yhe.4.1315503358366; Thu, 08 Sep 2011 10:35:58 -0700 (PDT) Received: by 10.236.202.41 with HTTP; Thu, 8 Sep 2011 10:35:58 -0700 (PDT) In-Reply-To: <001501cc6cc3$3649a3c0$a2dceb40$@devshock.com> References: <001501cc6cc3$3649a3c0$a2dceb40$@devshock.com> Date: Thu, 8 Sep 2011 12:35:58 -0500 Message-ID: Subject: Re: how fast index works? From: Merlin Moncure To: Anibal David Acosta Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 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, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201109/80 X-Sequence-Number: 44856 On Tue, Sep 6, 2011 at 1:31 PM, 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? The problem with large datasets does not come from the index, but that they increase cache pressure. On today's typical servers it's all about cache, and the fact that disks (at least non ssd drives) are several orders of magnitude slower than memory. Supposing you had infinite memory holding your data files in cache or infinitely fast disks, looking up a record from a trillion record table would still be faster than reading a record from a hundred record table that had to fault to a spinning disk to pull up the data. merlin From hghoury@gmail.com Thu Sep 8 21:04:21 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 92F25B5DC1C for ; Thu, 8 Sep 2011 18:04:21 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 83131-05 for ; Thu, 8 Sep 2011 21:04:14 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gw0-f49.google.com (mail-gw0-f49.google.com [74.125.83.49]) by mail.postgresql.org (Postfix) with ESMTP id EAD9DB5DC1B for ; Thu, 8 Sep 2011 18:04:13 -0300 (ADT) Received: by gwb1 with SMTP id 1so416633gwb.22 for ; Thu, 08 Sep 2011 14:04:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=zOTAtsbSW9ycQHHmzL0SaCTt69Jmkz78giZOiwS0eRk=; b=DMIIKk7Lxihb9GjGcXY6h3DU/qMQ8XC5GL+CnqlfnYLOQF4x+7bKv+avyuzq0W5js+ m4xy2u1Vf3j+L28ZeHarvZH1B/OrxoLPhxc65KoFZ1VgHqyurJ0MfbDslxmP3prvkmd0 Jq8GotojZP4XIzwswCuzU4YLDItzhCfVyIUv0= MIME-Version: 1.0 Received: by 10.236.79.197 with SMTP id i45mr7672432yhe.28.1315515853856; Thu, 08 Sep 2011 14:04:13 -0700 (PDT) Received: by 10.236.108.141 with HTTP; Thu, 8 Sep 2011 14:04:13 -0700 (PDT) In-Reply-To: <4E66C2EA.2020109@ringerc.id.au> References: <001501cc6cc3$3649a3c0$a2dceb40$@devshock.com> <4E66C2EA.2020109@ringerc.id.au> Date: Fri, 9 Sep 2011 09:04:13 +1200 Message-ID: Subject: Re: how fast index works? From: Hany ABOU-GHOURY To: Craig Ringer Cc: Anibal David Acosta , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=20cf30050d722a608804ac7467b1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201109/83 X-Sequence-Number: 44859 --20cf30050d722a608804ac7467b1 Content-Type: text/plain; charset=ISO-8859-1 Hi all, I am working on project to migrate PostgreSQL from V8.2 to 9.0 and optimise the new DB has any one done some thing like that before ? my main Task is the Optimisation part so please share some thoughts Regards Hany On Wed, Sep 7, 2011 at 1:03 PM, Craig Ringer wrote: > 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 > --20cf30050d722a608804ac7467b1 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi all,
I am working on project to migrate PostgreSQL from V8.2 to 9.0 and opt= imise the new DB
has any one done some thing like that before ?
my main Task is the Optimisation part so please share some thoughts
=A0
Regards
=A0
Hany

On Wed, Sep 7, 2011 at 1:03 PM, Craig Ringer <ringerc@ringerc= .id.au> wrote:
On 7/09/2011 2:31 AM, Anibal Davi= d Acosta wrote:=20

Hi everyone,

=A0

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?<= u>



It'= s not that simple. In addition to the performance scaling Craig James menti= oned, there are cache effects.

Your 500,000 row index might fit enti= rely 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 fi= t entirely in RAM, or competes for cache space with other things so it isn&= #39;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* c= aches, including cached disk data from running programs, the PostgreSQL sys= tem catalogs, etc. That makes it a rather unrealistic test when the only th= ing you really want to remove from cache is your index and the table associ= ated with it.

The best way to test whether data of a certain size will perform well i= s to create dummy data of that size and test with it. Anything else is gues= swork.

--
Craig Ringer

--20cf30050d722a608804ac7467b1-- From Kevin.Grittner@wicourts.gov Thu Sep 8 21:30:51 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 5AB87B5DC1A for ; Thu, 8 Sep 2011 18:30:51 -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 21582-03 for ; Thu, 8 Sep 2011 21:30:45 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id B60B4B5DC17 for ; Thu, 8 Sep 2011 18:30:43 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Thu, 08 Sep 2011 16:30:30 -0500 Message-Id: <4E68EDA00200002500040F98@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Thu, 08 Sep 2011 16:30:24 -0500 From: "Kevin Grittner" To: "Hany ABOU-GHOURY" Cc: Subject: Re: how fast index works? References: <001501cc6cc3$3649a3c0$a2dceb40$@devshock.com> <4E66C2EA.2020109@ringerc.id.au> In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.404 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-0.504 X-Spam-Level: X-Archive-Number: 201109/84 X-Sequence-Number: 44860 Hany ABOU-GHOURY wrote: > I am working on project to migrate PostgreSQL from V8.2 to 9.0 and > optimise the new DB Please don't hijack a thread to start a new topic. Start a new thread with a subject line which describes the new topic. -Kevin