X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id ADE28D1D38F for ; Fri, 12 Dec 2003 21:11:43 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 96734-03 for ; Fri, 12 Dec 2003 17:11:13 -0400 (AST) Received: from linda-2.paradise.net.nz (bm-2a.paradise.net.nz [202.0.58.21]) by svr1.postgresql.org (Postfix) with ESMTP id 8BBAED1BCB8 for ; Fri, 12 Dec 2003 17:11:11 -0400 (AST) Received: from smtp-1.paradise.net.nz (smtp-1a.paradise.net.nz [202.0.32.194]) by linda-2.paradise.net.nz (Paradise.net.nz) with ESMTP id <0HPS00JS3W6N6P@linda-2.paradise.net.nz> for pgsql-performance@postgresql.org; Sat, 13 Dec 2003 10:11:12 +1300 (NZDT) Received: from paradise.net.nz (218-101-14-127.paradise.net.nz [218.101.14.127]) by smtp-1.paradise.net.nz (Postfix) with ESMTP id 188B982768; Sat, 13 Dec 2003 10:11:11 +1300 (NZDT) Date: Sat, 13 Dec 2003 10:11:29 +1300 From: Mark Kirkwood Subject: Re: Performance related to size of tables In-reply-to: <20031212180449.697FD6C58F@tino.sinectis.com.ar> To: nbarraza@uolsinectis.com.ar Cc: pgsql-performance@postgresql.org Message-id: <3FDA2F01.10205@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 8BIT X-Accept-Language: en-us, en User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.5) Gecko/20031213 References: <20031212180449.697FD6C58F@tino.sinectis.com.ar> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/205 X-Sequence-Number: 5065 If you want to speed up the elapsed times, then the first thing would be to attempt to reduce the IO using some indexes, e.g. on test1(anumber), test2(anumber), test3((anumber%13)), test3((anumber%5)) and test4((anumber%27)) However if you wish to keep hammering the IO then the you would not use any indexes. However elapsed times for operations like: CREATE TABLE test4 AS SELECT ... FROM test1 JOIN test2 ON test1.anumber=test2.anumber; are going to increase non linearly with the size of the source table test1 (unless there are indexes on the anumber columns). I think this particular test is designed as a testbed for measuring IO performance - as opposed to Postgresql performance. regards Mark nbarraza@uolsinectis.com.ar wrote: >Hi everyone, >I found that performance get worse as the size of a given table >increases. I mean, for example I´ve just run some scripts shown in > >http://www.potentialtech.com/wmoran/postgresql.php > >I understand that those scripts are designed to see the behavior of postgresql under different filesystems. However, since them generate >a lot of I/O activity, I think they can be used to adjust some >configuration parameters. In that way, I increased the number of tuples inserted in the initial table to 2000000 and 3000000. What >I saw is that the running time goes from 3 min., to 11 min. My question is, is it possible to use that test to tune >some parameters?, if the answer is yes, what parameters should I change to get shorter running times? > > > >