在pg上作tpch测试步骤

显示全部楼层 · 2012-5-21 10:19:41

-bash-3.2$ ./initdb --encoding=utf8 -D /user1/postgresql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale C.
The default text search configuration will be set to "english".
creating directory /user1/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /user1/postgresql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.
Success. You can now start the database server using:
./postgres -D /user1/postgresql/data
or
./pg_ctl -D /user1/postgresql/data -l logfile start
-bash-3.2$ ./postgres -D /user1/postgresql/data &
[1] 27388
-bash-3.2$ LOG:database system was shut down at 2011-04-24 12:57:09 CST
LOG:autovacuum launcher started
LOG:database system is ready to accept connections
-bash-3.2$ createdb pgdb
CREATE DATABASE
-bash-3.2$ psql pgdb
Welcome to psql 8.1.18 (server 9.0.4), the PostgreSQL interactive terminal.
Type:\copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
WARNING:You are connected to a server with major version 9.0,
but your psql client is major version 8.1.Some backslash commands,
such as \d, might not work properly.
pgdb=# \i /user1/postgresql/dss.ddl
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
pgdb=# \d

List of relations
Schema | Name | Type|Owner
--------+----------+-------+----------
public | customer | table | postgres
public | lineitem | table | postgres
public | nation | table | postgres
public | orders | table | postgres
public | part | table | postgres
public | partsupp | table | postgres
public | region | table | postgres
public | supplier | table | postgres
(8 rows)

千问 · 2012-5-21 10:19:41

-bash-3.2$ ls -l /user1/app/oradata/tpch2/*tbl.gz
-rw-r--r-- 1 oracle oinstall 90373097 Feb2 19:10 /user1/app/oradata/tpch2/customer.tbl.gz
-rw-r--r-- 1 oracle oinstall 2304949340 Feb2 19:17 /user1/app/oradata/tpch2/lineitem.tbl.gz
-rw-r--r-- 1 oracle oinstall498102977 Feb2 19:19 /user1/app/oradata/tpch2/orders.tbl.gz
-rw-r--r-- 1 oracle oinstall287463641 Feb2 19:20 /user1/app/oradata/tpch2/partsupp.tbl.gz
-rw-r--r-- 1 oracle oinstall270 Feb2 19:20 /user1/app/oradata/tpch2/region.tbl.gz
-rw-r--r-- 1 oracle oinstall5657118 Feb2 19:20 /user1/app/oradata/tpch2/supplier.tbl.gz
-bash-3.2$ pwd
/opt/postgres/9.0/bin
-bash-3.2$ gzip --stdout -d /user1/app/oradata/tpch2/region.tbl.gz
0|AFRICA|lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to |
1|AMERICA|hs use ironic, even requests. s|
2|ASIA|ges. thinly even pinto beans ca|
3|EUROPE|ly final courts cajole furiously final excuse|
4|MIDDLE EAST|uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl|
要删除行尾|，但最后1行好像不能删除
-bash-3.2$ gzip --stdout -d /user1/app/oradata/tpch2/region.tbl.gz |sed 's/|\r/\r/'
0|AFRICA|lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to
1|AMERICA|hs use ironic, even requests. s
2|ASIA|ges. thinly even pinto beans ca
3|EUROPE|ly final courts cajole furiously final excuse
4|MIDDLE EAST|uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl

千问 · 2012-5-21 10:19:41

看错了，最后1行的|删除的
-bash-3.2$ gzip --stdout -d /user1/app/oradata/tpch2/region.tbl.gz |sed 's/|\r/\r/' > /user1/daa &
[2] 29069
-bash-3.2$ psql pgdb
pgdb=# \timing
Timing is on.
pgdb=# COPY region FROM '/user1/daa' WITH DELIMITER AS '|';
COPY 5
Time: 3.085 ms
pgdb=# select * from region;
r_regionkey |
r_name |
r_comment

-------------+---------------------------+---------------------------------------------------------------------------------------------------------------------
0 | AFRICA
| lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to
1 | AMERICA
| hs use ironic, even requests. s
2 | ASIA
| ges. thinly even pinto beans ca
3 | EUROPE
| ly final courts cajole furiously final excuse
4 | MIDDLE EAST
| uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl
(5 rows)
Time: 1.574 ms

千问 · 2012-5-21 10:19:41

-bash-3.2$ gzip --stdout -d /user1/app/oradata/tpch2/partsupp.tbl.gz |sed 's/|\r/\r/' > /user1/daa &
[3] 29239
-bash-3.2$ psql pgdb
Welcome to psql 8.1.18 (server 9.0.4), the PostgreSQL interactive terminal.
Type:\copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
WARNING:You are connected to a server with major version 9.0,
but your psql client is major version 8.1.Some backslash commands,
such as \d, might not work properly.
pgdb=# \timing
Timing is on.
pgdb=# copy partsupp FROM '/user1/daa' WITH DELIMITER AS '|';
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
COPY 8000000
Time: 47797.398 ms
改大后没有变化

-bash-3.2$ cd /user1/postgresql/data
-bash-3.2$ ls -l
total 76
-rw------- 1 postgres postgres 4 Apr 24 12:57 PG_VERSION
drwx------ 6 postgres postgres4096 Apr 24 12:58 base
drwx------ 2 postgres postgres4096 Apr 24 14:10 global
drwx------ 2 postgres postgres4096 Apr 24 12:57 pg_clog
-rw------- 1 postgres postgres3939 Apr 24 12:57 pg_hba.conf
-rw------- 1 postgres postgres1636 Apr 24 12:57 pg_ident.conf
drwx------ 4 postgres postgres4096 Apr 24 12:57 pg_multixact
drwx------ 2 postgres postgres4096 Apr 24 12:58 pg_notify
drwx------ 2 postgres postgres4096 Apr 24 14:10 pg_stat_tmp
drwx------ 2 postgres postgres4096 Apr 24 12:57 pg_subtrans
drwx------ 2 postgres postgres4096 Apr 24 12:57 pg_tblspc
drwx------ 2 postgres postgres4096 Apr 24 12:57 pg_twophase
drwx------ 3 postgres postgres4096 Apr 24 14:10 pg_xlog
-rw------- 1 postgres postgres 17935 Apr 24 12:57 postgresql.conf
-rw------- 1 postgres postgres61 Apr 24 12:58 postmaster.opts
-bash-3.2$ cat postgresql.conf|grep checkpoint_segments
#checkpoint_segments = 3
# in logfile segments, min 1, 16MB each
vi postgresql.conf
-bash-3.2$ cat /user1/postgresql/data/postgresql.conf|grep checkpoint_segments
#checkpoint_segments = 30
# in logfile segments, min 1, 16MB each
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
COPY 8000000
Time: 46457.382 ms

千问 · 2012-5-21 10:19:41

-bash-3.2$ ./pg_ctl -D /user1/postgresql/data stop
LOG:received smart shutdown request
waiting for server to shut down....LOG:autovacuum launcher shutting down
LOG:shutting down
LOG:database system is shut down
done
server stopped
[1]+Done
./postgres -D /user1/postgresql/data

-bash-3.2$ vi postgresql.conf
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.)Whitespace may be used.Comments are introduced with
# "#" anywhere on a line.The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload".Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:kB = kilobytesTime units:ms= milliseconds
#
MB = megabytes
s = seconds
#
GB = gigabytes
min = minutes
#
h = hours
#
d = days

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory

# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file

# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file

# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
/checkpoint_segments
# - Settings -
#wal_level = minimal
# minimal, archive, or hot_standby

# (change requires restart)
#fsync = on
# turns forced synchronization on or off
#synchronous_commit = on
# immediate fsync at commit
#wal_sync_method = fsync
# the default is the first option

# supported by the operating system:

# open_datasync

# fdatasync (default on Linux)

# fsync

# fsync_writethrough

# open_sync
#full_page_writes = on
# recover from partial page writes
#wal_buffers = 64kB
# min 32kB

# (change requires restart)
#wal_writer_delay = 200ms
# 1-10000 milliseconds
#commit_delay = 0
# range 0-100000, in microseconds
#commit_siblings = 5
# range 1-1000
# - Checkpoints -
#checkpoint_segments = 30
# in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min
# range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s
# 0 disables
# - Archiving -
#archive_mode = off
# allows archiving to be done

# (change requires restart)
#archive_command = '' # command to use to archive a logfile segment
#archive_timeout = 0
# force a logfile segment switch after this

# number of seconds; 0 disables
# - Streaming Replication -
#max_wal_senders = 0
# max number of walsender processes

# (change requires restart)
#wal_sender_delay = 200ms # walsender cycle time, 1-10000 milliseconds
#wal_keep_segments = 0
# in logfile segments, 16MB each; 0 disables
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
# - Standby Servers -
#hot_standby = off
# "on" allows queries during recovery

# (change requires restart)

-bash-3.2$ cd /opt/postgres/9.0
-bash-3.2$ cd bin
-bash-3.2$ ./postgres -D /user1/postgresql/data &
[1] 29763
-bash-3.2$ LOG:database system was shut down at 2011-04-24 14:10:25 CST
LOG:autovacuum launcher started
LOG:database system is ready to accept connections

千问 · 2012-5-21 10:19:41

-bash-3.2$ gzip --stdout -d /user1/app/oradata/tpch2/lineitem.tbl.gz |sed 's/|\r/\r/' > /user1/daa &
[2] 30036
-bash-3.2$ psql pgdb
Welcome to psql 8.1.18 (server 9.0.4), the PostgreSQL interactive terminal.
Type:\copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
WARNING:You are connected to a server with major version 9.0,
but your psql client is major version 8.1.Some backslash commands,
such as \d, might not work properly.
pgdb=# \timing
Timing is on.
pgdb=# COPY lineitem FROM '/user1/daa' WITH DELIMITER AS '|';
LOG:checkpoints are occurring too frequently (3 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
COPY 59986052
Time: 555966.304 ms
pgdb=# \q
[2]+Done
gzip --stdout -d /user1/app/oradata/tpch2/lineitem.tbl.gz | sed 's/|\r/\r/' > /user1/daa
-bash-3.2$ gzip --stdout -d /user1/app/oradata/tpch2/orders.tbl.gz | sed 's/|\r/\r/' > /user1/daa
&
-bash-3.2$ ps -ef|grep gzip
-bash-3.2$ gzip --stdout -d /user1/app/oradata/tpch2/orders.tbl.gz | sed 's/|\r/\r/' > /user1/daa &
[2] 30416
-bash-3.2$ psql pgdb
Welcome to psql 8.1.18 (server 9.0.4), the PostgreSQL interactive terminal.
Type:\copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
WARNING:You are connected to a server with major version 9.0,
but your psql client is major version 8.1.Some backslash commands,
such as \d, might not work properly.
pgdb=# \timing
Timing is on.
pgdb=# COPY orders FROM '/user1/daa' WITH DELIMITER AS '|';
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
COPY 15000000
Time: 100340.255 ms
pgdb=# \q
[2]+Done
gzip --stdout -d /user1/app/oradata/tpch2/orders.tbl.gz | sed 's/|\r/\r/' > /user1/daa
-bash-3.2$ gzip --stdout -d /user1/app/oradata/tpch2/customer.tbl.gz | sed 's/|\r/\r/' > /user1/daa &
[2] 30496
-bash-3.2$ psql pgdb
Welcome to psql 8.1.18 (server 9.0.4), the PostgreSQL interactive terminal.
Type:\copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
WARNING:You are connected to a server with major version 9.0,
but your psql client is major version 8.1.Some backslash commands,
such as \d, might not work properly.
pgdb=# \timing
Timing is on.
pgdb=# COPY customer FROM '/user1/daa' WITH DELIMITER AS '|';
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (1 second apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
COPY 1500000
Time: 12022.593 ms
pgdb=# \q
[2]+Done
gzip --stdout -d /user1/app/oradata/tpch2/customer.tbl.gz | sed 's/|\r/\r/' > /user1/daa
-bash-3.2$ gzip --stdout -d /user1/app/oradata/tpch2/supplier.tbl.gz | sed 's/|\r/\r/' > /user1/daa &
[2] 30547
-bash-3.2$ psql pgdb
Welcome to psql 8.1.18 (server 9.0.4), the PostgreSQL interactive terminal.
Type:\copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
WARNING:You are connected to a server with major version 9.0,
but your psql client is major version 8.1.Some backslash commands,
such as \d, might not work properly.
pgdb=# \timing
Timing is on.
pgdb=# COPY supplier FROM '/user1/daa' WITH DELIMITER AS '|';
COPY 100000
Time: 695.210 ms
pgdb=# \q
[2]+Done
gzip --stdout -d /user1/app/oradata/tpch2/supplier.tbl.gz | sed 's/|\r/\r/' > /user1/daa
-bash-3.2$ cat /user1/app/oradata/tpch2/nation.tbl | sed 's/|\r/\r/' > /user1/daa &
[2] 30600
-bash-3.2$ psql pgdb
Welcome to psql 8.1.18 (server 9.0.4), the PostgreSQL interactive terminal.
Type:\copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
WARNING:You are connected to a server with major version 9.0,
but your psql client is major version 8.1.Some backslash commands,
such as \d, might not work properly.
pgdb=# COPY nation FROM '/user1/daa' WITH DELIMITER AS '|';
COPY 25
pgdb=# select count(*) from nation;
count
-------
25
(1 row)
pgdb=# \timing
Timing is on.
pgdb=# select count(*) from region;
count
-------
5
(1 row)
Time: 0.805 ms
pgdb=# select count(*) from partsupp;
count
---------
8000000
(1 row)
Time: 7417.778 ms
pgdb=# select count(*) from supplier;
count
--------
100000
(1 row)
Time: 26.184 ms
pgdb=# select count(*) from customer;
count
---------
1500000
(1 row)
Time: 433.348 ms
pgdb=# select count(*) from orders;
count
----------
15000000
(1 row)
Time: 12530.868 ms
pgdb=# select count(*) from lineitem;
count
----------
59986052
(1 row)
Time: 53526.482 ms
pgdb=# \q
[2]+Done
cat /user1/app/oradata/tpch2/nation.tbl | sed 's/|\r/\r/' > /user1/daa
-bash-3.2$ cat /user1/app/oradata/tpch2/part.tbl | sed 's/|\r/\r/' > /user1/daa &
[2] 30699
-bash-3.2$ psql pgdb
Welcome to psql 8.1.18 (server 9.0.4), the PostgreSQL interactive terminal.
Type:\copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
WARNING:You are connected to a server with major version 9.0,
but your psql client is major version 8.1.Some backslash commands,
such as \d, might not work properly.
pgdb=# \timing
Timing is on.
pgdb=# copy partFROM '/user1/daa' WITH DELIMITER AS '|';
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
LOG:checkpoints are occurring too frequently (2 seconds apart)
HINT:Consider increasing the configuration parameter "checkpoint_segments".
COPY 2000000
Time: 15695.787 ms
pgdb=# select count(*) from part;
count
---------
2000000
(1 row)
Time: 1871.460 ms

统计分析
pgdb=# VACUUM ANALYZE;
VACUUM
Time: 76961.460 ms
pgdb=# select count(*) from partsupp;
count
---------
8000000
(1 row)
Time: 1686.777 ms
pgdb=# select count(*) from orders;
count
----------
15000000
(1 row)
Time: 2624.166 ms
pgdb=# select count(*) from lineitem;
count
----------
59986052
(1 row)
Time: 12061.884 ms
pgdb=# select count(*) from part;
count
---------
2000000
(1 row)
Time: 419.798 ms
pgdb=#

千问 · 2012-5-21 10:19:41

pgdb=# \i /user1/postgresql/tpchQ-postgres.txt
l_returnflag | l_linestatus | sum_qty|sum_base_price| sum_disc_price |sum_charge| avg_qty | avg_price|avg_disc| count_order
--------------+--------------+--------------+------------------+--------------------+----------------------+---------------------+--------------------+------------------------+-------------
A
| F
| 377518399.00 |566065727797.25 |537759104278.0656 |559276670892.116819 | 25.5009751030070973 | 38237.151008958546 | 0.05000657454024320463 |14804077
N
| F
| 9851614.00 | 14767438399.17 | 14028805792.2114 | 14590490998.366737 | 25.5224483028409474 | 38257.810660081140 | 0.04997336773765667180 |385998
N
| O
| 731368004.00 | 1096674315157.26 | 1041836574901.1892 | 1083522615974.869816 | 25.4975234176794189 | 38233.117772936887 | 0.04999893982279387268 |28683884
R
| F
| 377732830.00 |566431054976.00 |538110922664.7677 |559634780885.086257 | 25.5083847896801383 | 38251.219273559761 | 0.04999679231408742045 |14808183
(4 rows)
Time: 379324.892 ms

千问 · 2012-5-21 10:19:41

pgdb=# \i /user1/postgresql/data/q2.txt
select

s_acctbal,

s_name,

n_name,

p_partkey,

p_mfgr,

s_address,

s_phone,

s_comment
from

part,

supplier,

partsupp,

nation,

region
where

p_partkey = ps_partkey

and s_suppkey = ps_suppkey

and p_size = 9

and p_type like '%TIN'

and s_nationkey = n_nationkey

and n_regionkey = r_regionkey

and r_name = 'MIDDLE EAST'

and ps_supplycost = (

select

min(ps_supplycost)

from

partsupp,

supplier,

nation,

region

where

p_partkey = ps_partkey

and s_suppkey = ps_suppkey

and s_nationkey = n_nationkey

and n_regionkey = r_regionkey

and r_name = 'MIDDLE EAST'

)
order by

s_acctbal desc,

n_name,

s_name,

p_partkey

limit 100;
(100 rows)
Time: 23803421.147 ms
pgdb=#

千问 · 2012-5-21 10:19:41

pgdb=# \i /user1/postgresql/data/dss.ri
ERROR:syntax error at or near "CONNECT" at character 1
STATEMENT:CONNECT TO TPCD;
psql:/user1/postgresql/data/dss.ri:4: ERROR:syntax error at or near "CONNECT" at character 1
psql:/user1/postgresql/data/dss.ri:4: LINE 1: CONNECT TO TPCD;
psql:/user1/postgresql/data/dss.ri:4: ^
psql:/user1/postgresql/data/dss.ri:18: NOTICE:ALTER TABLE / ADD PRIMARY KEY will create implicit index "region_pkey" for table "region"
ALTER TABLE
Time: 92.402 ms
psql:/user1/postgresql/data/dss.ri:22: NOTICE:ALTER TABLE / ADD PRIMARY KEY will create implicit index "nation_pkey" for table "nation"
ALTER TABLE
Time: 1.972 ms
ERROR:syntax error at or near "NATION_FK1" at character 37
STATEMENT:ALTER TABLE NATION
ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references REGION;
psql:/user1/postgresql/data/dss.ri:25: ERROR:syntax error at or near "NATION_FK1" at character 37
psql:/user1/postgresql/data/dss.ri:25: LINE 2: ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references REGION;
psql:/user1/postgresql/data/dss.ri:25:
^
WARNING:there is no transaction in progress
psql:/user1/postgresql/data/dss.ri:27: WARNING:there is no transaction in progress
COMMIT
Time: 1.003 ms
psql:/user1/postgresql/data/dss.ri:31: NOTICE:ALTER TABLE / ADD PRIMARY KEY will create implicit index "part_pkey" for table "part"
ALTER TABLE
Time: 3782.712 ms
WARNING:there is no transaction in progress
psql:/user1/postgresql/data/dss.ri:33: WARNING:there is no transaction in progress
COMMIT
Time: 0.500 ms
psql:/user1/postgresql/data/dss.ri:37: NOTICE:ALTER TABLE / ADD PRIMARY KEY will create implicit index "supplier_pkey" for table "supplier"
ALTER TABLE
Time: 111.763 ms
ERROR:syntax error at or near "SUPPLIER_FK1" at character 39
STATEMENT:ALTER TABLE SUPPLIER
ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references NATION;
psql:/user1/postgresql/data/dss.ri:40: ERROR:syntax error at or near "SUPPLIER_FK1" at character 39
psql:/user1/postgresql/data/dss.ri:40: LINE 2: ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references NATION...
psql:/user1/postgresql/data/dss.ri:40:
^
WARNING:there is no transaction in progress
psql:/user1/postgresql/data/dss.ri:42: WARNING:there is no transaction in progress
COMMIT
Time: 0.228 ms
psql:/user1/postgresql/data/dss.ri:46: NOTICE:ALTER TABLE / ADD PRIMARY KEY will create implicit index "partsupp_pkey" for table "partsupp"
ALTER TABLE
Time: 15464.902 ms
WARNING:there is no transaction in progress
psql:/user1/postgresql/data/dss.ri:48: WARNING:there is no transaction in progress
COMMIT
Time: 0.489 ms
psql:/user1/postgresql/data/dss.ri:52: NOTICE:ALTER TABLE / ADD PRIMARY KEY will create implicit index "customer_pkey" for table "customer"
ALTER TABLE
Time: 2647.225 ms
ERROR:syntax error at or near "CUSTOMER_FK1" at character 39
STATEMENT:ALTER TABLE CUSTOMER
ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references NATION;
psql:/user1/postgresql/data/dss.ri:55: ERROR:syntax error at or near "CUSTOMER_FK1" at character 39
psql:/user1/postgresql/data/dss.ri:55: LINE 2: ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references NATION...
psql:/user1/postgresql/data/dss.ri:55:
^
WARNING:there is no transaction in progress
psql:/user1/postgresql/data/dss.ri:57: WARNING:there is no transaction in progress
COMMIT
Time: 0.278 ms
psql:/user1/postgresql/data/dss.ri:61: NOTICE:ALTER TABLE / ADD PRIMARY KEY will create implicit index "lineitem_pkey" for table "lineitem"
ALTER TABLE
Time: 111591.310 ms
WARNING:there is no transaction in progress
psql:/user1/postgresql/data/dss.ri:63: WARNING:there is no transaction in progress
COMMIT
Time: 0.197 ms
psql:/user1/postgresql/data/dss.ri:67: NOTICE:ALTER TABLE / ADD PRIMARY KEY will create implicit index "orders_pkey" for table "orders"
ALTER TABLE
Time: 25732.725 ms
WARNING:there is no transaction in progress
psql:/user1/postgresql/data/dss.ri:69: WARNING:there is no transaction in progress
COMMIT
Time: 0.168 ms
ERROR:syntax error at or near "PARTSUPP_FK1" at character 39
STATEMENT:ALTER TABLE PARTSUPP
ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references SUPPLIER;
psql:/user1/postgresql/data/dss.ri:73: ERROR:syntax error at or near "PARTSUPP_FK1" at character 39
psql:/user1/postgresql/data/dss.ri:73: LINE 2: ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references SUPPLIE...
psql:/user1/postgresql/data/dss.ri:73:
^
WARNING:there is no transaction in progress
psql:/user1/postgresql/data/dss.ri:75: WARNING:there is no transaction in progress
COMMIT
Time: 0.086 ms
ERROR:syntax error at or near "PARTSUPP_FK2" at character 39
STATEMENT:ALTER TABLE PARTSUPP
ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references PART;
psql:/user1/postgresql/data/dss.ri:78: ERROR:syntax error at or near "PARTSUPP_FK2" at character 39
psql:/user1/postgresql/data/dss.ri:78: LINE 2: ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references PART;
psql:/user1/postgresql/data/dss.ri:78:
^
WARNING:there is no transaction in progress
psql:/user1/postgresql/data/dss.ri:80: WARNING:there is no transaction in progress
COMMIT
Time: 0.085 ms
ERROR:syntax error at or near "ORDERS_FK1" at character 37
STATEMENT:ALTER TABLE ORDERS
ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references CUSTOMER;
psql:/user1/postgresql/data/dss.ri:84: ERROR:syntax error at or near "ORDERS_FK1" at character 37
psql:/user1/postgresql/data/dss.ri:84: LINE 2: ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references CUSTOMER;
psql:/user1/postgresql/data/dss.ri:84:
^
WARNING:there is no transaction in progress
psql:/user1/postgresql/data/dss.ri:86: WARNING:there is no transaction in progress
COMMIT
Time: 0.084 ms
ERROR:syntax error at or near "LINEITEM_FK1" at character 39
STATEMENT:ALTER TABLE LINEITEM
ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY)references ORDERS;
psql:/user1/postgresql/data/dss.ri:90: ERROR:syntax error at or near "LINEITEM_FK1" at character 39
psql:/user1/postgresql/data/dss.ri:90: LINE 2: ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY)references ORDERS...
psql:/user1/postgresql/data/dss.ri:90:
^
WARNING:there is no transaction in progress
psql:/user1/postgresql/data/dss.ri:92: WARNING:there is no transaction in progress
COMMIT
Time: 0.068 ms
ERROR:syntax error at or near "LINEITEM_FK2" at character 39
STATEMENT:ALTER TABLE LINEITEM
ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references

PARTSUPP;
psql:/user1/postgresql/data/dss.ri:96: ERROR:syntax error at or near "LINEITEM_FK2" at character 39
psql:/user1/postgresql/data/dss.ri:96: LINE 2: ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) reference...
psql:/user1/postgresql/data/dss.ri:96:
^
WARNING:there is no transaction in progress
psql:/user1/postgresql/data/dss.ri:98: WARNING:there is no transaction in progress
COMMIT
Time: 0.088 ms
pgdb=#
pgdb=# VACUUM ANALYZE;
VACUUM
Time: 5025.876 ms
pgdb=# \i /user1/postgresql/data/q1.txt
l_returnflag | l_linestatus | sum_qty|sum_base_price| sum_disc_price |sum_charge| avg_qty | avg_price|avg_disc| count_order
--------------+--------------+--------------+------------------+--------------------+----------------------+---------------------+--------------------+------------------------+-------------
A
| F
| 377518399.00 |566065727797.25 |537759104278.0656 |559276670892.116819 | 25.5009751030070973 | 38237.151008958546 | 0.05000657454024320463 |14804077
N
| F
| 9851614.00 | 14767438399.17 | 14028805792.2114 | 14590490998.366737 | 25.5224483028409474 | 38257.810660081140 | 0.04997336773765667180 |385998
N
| O
| 731368004.00 | 1096674315157.26 | 1041836574901.1892 | 1083522615974.869816 | 25.4975234176794189 | 38233.117772936887 | 0.04999893982279387268 |28683884
R
| F
| 377732830.00 |566431054976.00 |538110922664.7677 |559634780885.086257 | 25.5083847896801383 | 38251.219273559761 | 0.04999679231408742045 |14808183
(4 rows)
Time: 381999.098 ms
pgdb=#

千问 · 2012-5-21 10:19:41

pgdb=# ALTER TABLE NATION
pgdb-# ADD CONSTRAINT NATION_FK1 FOREIGN KEY (N_REGIONKEY) references REGION;
ALTER TABLE
Time: 52.767 ms
pgdb=# ALTER TABLE SUPPLIER
pgdb-# ADD CONSTRAINT SUPPLIER_FK1 FOREIGN KEY(S_NATIONKEY) references NATION;
ALTER TABLE
Time: 48.209 ms
pgdb=# ALTER TABLE CUSTOMER
pgdb-# ADD CONSTRAINT CUSTOMER_FK1FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references NATION;
ERROR:syntax error at or near "KEY" at character 57
STATEMENT:ALTER TABLE CUSTOMER
ADD CONSTRAINT CUSTOMER_FK1FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references NATION;
ERROR:syntax error at or near "KEY" at character 57
LINE 2: ADD CONSTRAINT CUSTOMER_FK1FOREIGN KEY CUSTOMER_FK1 (C_NATIO...

^
pgdb=# ALTER TABLE CUSTOMER
pgdb-# ADD CONSTRAINT CUSTOMER_FK1 FOREIGN KEY(C_NATIONKEY) references NATION;
ALTER TABLE
Time: 700.381 ms
pgdb=# ALTER TABLE PARTSUPP
pgdb-# ADD CONSTRAINT PARTSUPP_FK2 FOREIGN KEY (PS_PARTKEY) references PART;
ALTER TABLE
Time: 8117.485 ms
pgdb=# ALTER TABLE ORDERS
pgdb-# ADD CONSTRAINT ORDERS_FK1 FOREIGN KEY(O_CUSTKEY) references CUSTOMER;
ALTER TABLE
Time: 20213.284 ms
pgdb=# ALTER TABLE LINEITEM
pgdb-# ADD CONSTRAINT LINEITEM_FK1 FOREIGN KEY (L_ORDERKEY)references ORDERS;
ALTER TABLE
Time: 60656.277 ms
pgdb=# ALTER TABLE LINEITEM
pgdb-# ADD CONSTRAINT LINEITEM_FK2 FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references
pgdb-# PARTSUPP;
ALTER TABLE
Time: 204054.318 ms
pgdb=# VACUUM ANALYZE;
VACUUM
Time: 5153.792 ms
pgdb=# \i /user1/postgresql/data/q2.txt

s_acctbal |
s_name |
n_name | p_partkey |
p_mfgr |
s_addres
s
| s_phone |
s_comment

-----------+---------------------------+---------------------------+-----------+---------------------------+------------------------
------------------+-----------------+-----------------------------------------------------------------------------------------------
-----
9998.20 | Supplier#000006343| EGYPT
| 1481328 | Manufacturer#3
| UIRkduD9Iv2sxX9ly6MPt

| 14-617-999-2300 | odolites. unusual packages was carefully reg
9995.59 | Supplier#000031731| IRAQ
| 1356691 | Manufacturer#4
| ihiaqm0S33pkU

| 21-420-388-3738 |against the special ideas are furiously furiously even platelet
9995.06 | Supplier#000027193| JORDAN
| 1977154 | Manufacturer#2
| kI2QBgD6qOD M2cvm5DHRWg
lie4iiezig6zKBOv, | 23-747-675-5470 | atelets! regular packages doubt furiously. carefully regular requests are thinly fluffily regu
l
9994.77 | Supplier#000095917| EGYPT
| 1620868 | Manufacturer#4
| mAIyuuHBLI hA1SfK z0G6H
4Zh2cq1 | 14-122-667-2946 | s are ironically boldly regular courts. thin, pending ideas along the final packages ha
9994.53 | Supplier#000021480| EGYPT
|221479 | Manufacturer#4
| peaej4D5vGn8rZLUII6pX3I
fNmA 2VcDalUl | 14-865-645-9658 | ts. slyly even foxes integrate

(100 rows)
Time: 12984.345 ms
pgdb=#
pgdb=#