X-Original-To: pgsql-advocacy-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP
	id 9C8FDD1B496; Sun, 25 Apr 2004 17:41:31 -0300 (ADT)
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 36285-04; Sun, 25 Apr 2004 17:41:31 -0300 (ADT)
Received: from candle.pha.pa.us (candle.pha.pa.us [207.106.42.251])
	by svr1.postgresql.org (Postfix) with ESMTP
	id 9D80FD1B4D9; Sun, 25 Apr 2004 17:41:28 -0300 (ADT)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.11.6) id i3PKfRJ28435;
	Sun, 25 Apr 2004 16:41:27 -0400 (EDT)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200404252041.i3PKfRJ28435@candle.pha.pa.us>
Subject: Re: [HACKERS] What can we learn from MySQL?
In-Reply-To: <200404251622.29066.peter_e@gmx.net>
To: Peter Eisentraut <peter_e@gmx.net>
Date: Sun, 25 Apr 2004 16:41:27 -0400 (EDT)
Cc: Rob <pgadmin@itsbeen.sent.com>, pgsql-advocacy@postgresql.org,
	pgsql-hackers@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL108 (25)]
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary=ELM1082925687-26829-0_
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No,
 hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200404/202
X-Sequence-Number: 4174

--ELM1082925687-26829-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII

Peter Eisentraut wrote:
> Rob wrote:
> > But I think there is room to go further, I don't see any reason why
> > that default install can't include example DBs,
> 
> One reason is that a useful example database would likely have a 
> download footprint of 10 MB or more.  Having this in the default 
> download would not be appreciated by many people.  Of course having 
> some example database available at all would be a good idea, but then 
> as a separate download.

Here is a little psql script I wrote to populate a table with random
data.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

--ELM1082925687-26829-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
Content-Disposition: inline; filename="/pgdev/perftest/perf.sh"

\set ECHO all
\timing
DROP TABLE perftest;
CREATE TABLE perftest (col text);

-- prime table with one row
INSERT INTO perftest VALUES ('0.364461265208414');

-- continously double the table size
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;

-- insert a constant in the middle of the table, for use later
INSERT INTO perftest VALUES ('0.608254158221304');
INSERT INTO perftest SELECT random()::text FROM perftest;
-- 32770 rows

-- vacuum, create index
VACUUM ANALYZE perftest;
CREATE INDEX i_perftest ON perftest (col);
-- reduce chance of checkpoint during tests
CHECKPOINT;

-- turn on logging
SET log_duration = TRUE;
SET client_min_messages = 'log';

-- run normal and prepared queries
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
PREPARE perftest_prep (text) AS SELECT col FROM perftest WHERE col = $1;
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');

-- first time the entire statement
SET log_statement_stats = TRUE;

-- run normal and prepared queries
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
PREPARE perftest_prep (text) AS SELECT col FROM perftest WHERE col = $1;
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');

-- now log each query stage
SET log_statement_stats = FALSE;
SET log_parser_stats = TRUE;
SET log_planner_stats = TRUE;
SET log_executor_stats = TRUE;

-- run normal and prepared queries
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');


--ELM1082925687-26829-0_--