本文档记录了在PostgreSQL上执行TPCH测试的过程，包括遇到的错误和解决方法。在尝试连接TPCD时出现语法错误，但成功创建了多个表的主键。之后，在添加外键时遇到问题，如'NATION_FK1'和'SUPPLIER_FK1'等。尽管有警告提示没有活动事务，仍进行了COMMIT操作。最后，执行了VACUUM ANALYZE以优化数据库状态。
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=#
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
