在pg上作tpch测试步骤

〇〇 · 发表于 2011-4-25 07:37

pgdb=#
pgdb=# \i /user1/postgresql/data/q3.txt
l_orderkey | revenue | o_orderdate | o_shippriority
------------+-------------+-------------+----------------
21996356 | 429022.2405 | 1995-03-26  |             0
31617348 | 427014.3344 | 1995-03-27  |             0
47577254 | 426741.3390 | 1995-03-18  |             0
21404195 | 424534.2231 | 1995-03-25  |             0
35312039 | 422403.5320 | 1995-03-05  |             0
34684896 | 417863.0392 | 1995-03-11  |             0
34575136 | 413225.0126 | 1995-03-26  |             0
47741510 | 412126.9054 | 1995-03-09  |             0
46891527 | 411812.3222 | 1995-03-25  |             0
11646177 | 410971.5546 | 1995-03-16  |             0
(10 rows)

Time: 52772.048 ms
pgdb=# \i /user1/postgresql/data/q4.txt
o_orderpriority | order_count
-----------------+-------------
1-URGENT       |    105830
2-HIGH       |    105465
3-MEDIUM       |    104928
4-NOT SPECIFIED |    104715
5-LOW          |    104977
(5 rows)

Time: 53281.759 ms
pgdb=# \i /user1/postgresql/data/q5.txt
      n_name          | revenue
---------------------------+----------------
CANADA                   | 545812773.7002
UNITED STATES          | 537504828.4756
ARGENTINA                | 530792211.9317
PERU                   | 523818935.3510
BRAZIL                   | 522301577.4584
(5 rows)

Time: 63178.844 ms
pgdb=# \i /user1/postgresql/data/q6.txt

〇〇 · 发表于 2011-4-25 09:04

pgdb=# \i /user1/postgresql/data/q6.txt
revenue
----------------
615647213.2901
(1 row)

Time: 26288.915 ms
pgdb=# \i /user1/postgresql/data/q7.txt
      supp_nation       |       cust_nation       | l_year | revenue
---------------------------+---------------------------+--------+----------------
ARGENTINA                | FRANCE                   | 1995 | 531156727.2505
ARGENTINA                | FRANCE                   | 1996 | 538746468.5274
FRANCE                   | ARGENTINA                | 1995 | 523667981.8327
FRANCE                   | ARGENTINA                | 1996 | 529761492.9053
(4 rows)

Time: 46573.129 ms
pgdb=# \i /user1/postgresql/data/q8.txt
o_year |    mkt_share
--------+------------------------
1995 | 0.03955424769916200685
1996 | 0.03846446765981474593
(2 rows)

Time: 35611.563 ms
pgdb=# \i /user1/postgresql/data/q9.txt
      nation          | o_year | sum_profit
---------------------------+--------+----------------
ALGERIA                | 1998 | 234330090.6783
ALGERIA                | 1997 | 391966649.0005
ALGERIA                | 1996 | 404034204.1157
ALGERIA                | 1995 | 403462843.9338
ALGERIA                | 1994 | 402784606.2745

VIETNAM                | 1993 | 411770777.7047
VIETNAM                | 1992 | 418446831.6627
(175 rows)

Time: 232154.820 ms
pgdb=# \i /user1/postgresql/data/q10.txt

c_custkey |    c_name    | revenue | c_acctbal |       n_name          |             c_address             |    c_phone    |                                                 c_comment
-----------+--------------------+-------------+-----------+---------------------------+---------------------------------------+-----------------+----------------------------------------------------------------------------------------------------------------
   29581 | Customer#000029581 | 783023.0226 | -629.61 | ARGENTINA                | t2GMv bgtIVPb4towKYQKBs6EGh,1s7BRf | 11-133-736-8171 | . quickly final accounts wake since the even instructions. regular, permanent accounts are against the final p
   76033 | Customer#000076033 | 777093.4350 | -718.33 | ALGERIA                | TqJsSBmjyoU0f8oXBMuqB9CQ7XB6XfZOpGL | 10-645-182-3231 | ay after the excuses. idly even accounts cajole slyly even dependencies; bold requests along the fo
   12559 | Customer#000012559 | 774645.0702 | 4544.71 | ALGERIA                | CeDTUfnkc6gFtSczzZdU8GBB5M63RsKA EQq  | 10-393-903-4435 |  to the bold foxes. fluffily express pinto beans sleep rut
518650 | Customer#000518650 | 755754.2812 | 1301.52 | JORDAN                   | FpRAPcdbM,R9                         | 23-844-399-3555 | ronic theodolites affix carefully pending deposits. furiously silent th
   73009 | Customer#000073009 | 755147.0832 | 3672.96 | INDONESIA                | I2DrfNjsLvlIdqFdRS5alxUccyCtlfUIR    | 19-448-422-5320 | g excuses? ironic, regular excuses wake slyly along
1192936 | Customer#001192936 | 746252.2597 | -234.16 | RUSSIA                   | dbHH,k2N4MhLVKu9XnJUDiNg             | 32-644-511-4374 | lites. special theodolites sleep carefully above th
1217080 | Customer#001217080 | 743551.7813 | 1433.07 | IRAQ                   | Cn2SuvW2f79ifa0eOwO0AsBFnOApmPcskqBxr | 21-217-359-4450 | . furiously express accounts run. ironic foxes wake. furiously final
122269 | Customer#000122269 | 741864.5670 | 9633.10 | EGYPT                   | 6G3yP9tENrVow hO4EQ9FVBQAWQPZwlp    | 14-751-252-2045 | uts lose blithely along the carefully ironic courts. regular request
1081825 | Customer#001081825 | 726607.0830 | 8857.62 | RUSSIA                   | k5g9X,WwuxurArkV9f2o39KpO,oLFGH    | 32-633-426-2292 | yly ironic dolphins. slyly fin
434047 | Customer#000434047 | 722019.1570 | -136.97 | UNITED STATES          | ztDQXRbCkDlxmZXB                   | 34-420-850-3020 | nd the quickly ironic asymptotes. bold, regular waters use slyly across the carefully
940375 | Customer#000940375 | 716583.2198 | 6942.30 | IRAN                   | v8,h5rcbqRNNFMjL                   | 20-499-624-6444 | ress slowly final requests. unusual
877852 | Customer#000877852 | 713514.9712 | -676.04 | FRANCE                   | D0KW9am9RA9zmgJNDzh89LDAVp0VLMK4Vgym  | 16-328-692-6607 | efully final packages sleep blithely. express, unusual theodolites wake about the bold instructions.
1163617 | Customer#001163617 | 708880.9301 | 1840.89 | IRAQ                   | 9yr2h99xclG2Bj                      | 21-252-822-9416 | kages. slyly regular requests are furiously even notornis. unusual dependen
1440790 | Customer#001440790 | 705130.8785 | 4687.66 | ARGENTINA                | wx9IUBdXv,QMjYsqd1lDiRBLC          | 11-873-816-8854 | ess, final requests affix carefully closely even
1361203 | Customer#001361203 | 696439.9980 | 4883.66 | RUSSIA                   | VaPtbhmoK2HcYyDp3cQOi1eLkY1KM2hUAQiI2 | 32-325-841-7813 | ns among the blithely express packages hinder furiously slyly unusual accounts! quickly special p
129811 | Customer#000129811 | 695478.0049 | 2454.96 | BRAZIL                   | wxCtrfcbRZNP                         | 12-163-974-8679 | bove the carefully quick deposits. quickly unusual accoun
316849 | Customer#000316849 | 694108.3440 | 3940.32 | JAPAN                   | 9ELkQix1NuQU9IyVdJ30SO1uh4          | 22-737-317-4236 | ts. silent ideas wake carefully among the ironic accounts. ironic excuses integrate
900199 | Customer#000900199 | 692994.9538 | 498.39 | INDONESIA                | JFFrLONQxM6eHQI6xBKJhbXGmj5K7ISpn    | 19-279-757-8505 | even requests haggle slyly against the f
116299 | Customer#000116299 | 692011.1336 | -753.07 | CHINA                   | iYhwvk,Nbm64dWOUFIz0AsoJywXSl2IQH    | 28-939-870-9524 |  haggle Tiresias. waters grow carefully. fluffily dogged dolphins sleep regular, ironic requests. perman
254452 | Customer#000254452 | 689498.7780 | 7459.78 | ROMANIA                | hcLk7NcxWq                         | 29-487-552-4072 | ggle; carefully bold deposits boost blithely among th
(20 rows)

Time: 51616.576 ms
pgdb=#
pgdb=# \i /user1/postgresql/data/q11.txt
ps_partkey | value
------------+-------
(0 rows)

Time: 7333.612 ms
pgdb=# \i /user1/postgresql/data/q12.txt

l_shipmode | high_line_count | low_line_count
------------+-----------------+----------------
FOB       |          62623 |       93407
TRUCK    |          62305 |       93899
(2 rows)

Time: 37057.697 ms
pgdb=#
pgdb=# \i /user1/postgresql/data/q13.txt
c_count | custdist
---------+----------
   0 | 500018
   10 | 66108
   9 | 65100
   11 | 62178
   8 | 58499
   12 | 55678
   13 | 49998
   7 | 46640
   19 | 46511
   18 | 46223
   20 | 45316
   14 | 45281
   17 | 45214
   16 | 44055
   15 | 43594
   21 | 42269
   22 | 38055
   6 | 32943
   23 | 32647
   24 | 26643
   25 | 21031
   5 | 19689
   26 | 16047
   27 | 11606
   4 |    9934
   28 |    8233
   29 |    5595
   3 |    4013
   30 |    3660
   31 |    2349
   32 |    1494
   2 |    1206
   33 |    857
   34 |    486
   35 |    267
   1 |    244
   36 |    159
   37 |    72
   38 |    45
   39 |    17
   40 |    12
   41 |       7
   42 |       3
   43 |       2
   46 |       1
   44 |       1
(46 rows)

Time: 49070.778 ms
p

〇〇 · 发表于 2011-4-25 09:28

〇〇 · 发表于 2011-4-25 09:35

pgdb=# \i /user1/postgresql/data/q17.txt


ERROR:  canceling statement due to user request
STATEMENT:  select
            sum(l_extendedprice) / 7.0 as avg_yearly
      from
            lineitem,
            part
      where
            p_partkey = l_partkey
            and p_brand = 'Brand#54'
            and p_container = 'LG BAG'
            and l_quantity < (
                     select
                              0.2 * avg(l_quantity)
                     from
                              lineitem
                     where
                              l_partkey = p_partkey
            );
Cancel request sent
psql:/user1/postgresql/data/q17.txt:17: ERROR:  canceling statement due to user request
pgdb=# \i /user1/postgresql/data/q18.txt


   c_name    | c_custkey | o_orderkey | o_orderdate | o_totalprice |  sum
--------------------+-----------+------------+-------------+--------------+--------
Customer#001287812 | 1287812 | 42290181 | 1997-11-26  | 558289.17 | 318.00
Customer#001172513 | 1172513 | 36667107 | 1997-06-06  | 550142.18 | 322.00
Customer#000571654 | 571654 | 21213895 | 1992-01-03  | 549380.08 | 327.00
Customer#000667882 | 667882 | 2199712 | 1996-09-30  | 542154.01 | 327.00
Customer#001471966 | 1471966 | 1263015 | 1997-02-02  | 540476.80 | 320.00
Customer#001101754 | 1101754 | 46794464 | 1992-04-28  | 532623.90 | 320.00

Customer#001199881 | 1199881 | 1544643 | 1997-09-20  | 406906.07 | 320.00
Customer#001188142 | 1188142 | 42997059 | 1997-03-15  | 404132.92 | 320.00
Customer#001288183 | 1288183 | 48943904 | 1996-07-22  | 398081.59 | 325.00
Customer#000114613 | 114613 | 59930883 | 1997-05-17  | 394335.49 | 319.00
(84 rows)

Time: 160399.655 ms
pgdb=#
pgdb=#
pgdb=# \i /user1/postgresql/data/q19.txt


revenue
---------------
38030748.8555
(1 row)

Time: 36403.114 ms

〇〇 · 发表于 2011-4-25 09:51

pgdb=# \i /user1/postgresql/data/q20.txt


ERROR:  canceling statement due to user request
STATEMENT:  select
            s_name,
            s_address
      from
            supplier,
            nation
      where
            s_suppkey in (
                     select
                              ps_suppkey
                     from
                              partsupp
                     where
                              ps_partkey in (
                                    select
                                             p_partkey
                                    from
                                             part
                                    where
                                             p_name like 'lime%'
                              )
                              and ps_availqty > (
                                    select
                                             0.5 * sum(l_quantity)
                                    from
                                             lineitem
                                    where
                                             l_partkey = ps_partkey
                                             and l_suppkey = ps_suppkey
                                             and l_shipdate >= date '1993-01-01'
                                             and l_shipdate < date '1993-01-01' + interval '1 year'
                              )
            )
            and s_nationkey = n_nationkey
            and n_name = 'VIETNAM'
      order by
            s_name;
Cancel request sent
psql:/user1/postgresql/data/q20.txt:37: ERROR:  canceling statement due to user request
pgdb=# \i /user1/postgresql/data/q21.txt


      s_name          | numwait
---------------------------+---------
Supplier#000024297       |    22
Supplier#000014526       |    21
Supplier#000050896       |    21
Supplier#000051263       |    21
Supplier#000021732       |    20
Supplier#000077039       |    20
Supplier#000091469       |    20
Supplier#000097945       |    20
Supplier#000005792       |    19
  Supplier#000002744       |    16
Supplier#000005145       |    16
(100 rows)

Time: 135721.222 ms
pgdb=#
pgdb=# \i /user1/postgresql/data/q22.txt



cntrycode | numcust | totacctbal
-----------+---------+-------------
10       | 9053 | 67752399.56
11       | 9006 | 67894525.19
19       | 9078 | 68177380.15
20       | 9240 | 69425181.57
22       | 8990 | 67271840.24
26       | 9013 | 67705001.84
27       | 9006 | 67600913.56
(7 rows)

Time: 16126.657 ms
pgdb=#
pgdb=#
pgdb=#
pgdb=#

〇〇 · 发表于 2011-4-25 09:57

加主键前
q1 379324.892 ms

q2 23803421.147 ms

〇〇 · 发表于 2011-4-25 10:03

加主键后
1 381999.098 ms
2 12984.345 ms
3 52772.048 ms
4 53281.759 ms
5 63178.844 ms
6 26288.915 ms
7 46573.129 ms
8 35611.563 ms
9 232154.820 ms
10 51616.576 ms

〇〇 · 发表于 2011-4-25 10:06

11 7333.612 ms
12 37057.697 ms
13 49070.778 ms
14 24971.051 ms
15 54417.590 ms
16 17657.497 ms
17
18 160399.655 ms
19 36403.114 ms
20
21 135721.222 ms
22 16126.657 ms

〇〇 · 发表于 2011-4-25 14:04

pg对interval的语法与别家不同,比如q10
pg:
select
c_custkey,
c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,
n_name,
c_address,
c_phone,
c_comment
from
customer,
orders,
lineitem,
nation
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate >= date '1993-03-01'
and o_orderdate < date '1993-03-01' + interval '3 month'
and l_returnflag = 'R'
and c_nationkey = n_nationkey
group by
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
order by
revenue desc
limit 20;
其他:
select
      c_custkey,
      c_name,
      sum(l_extendedprice * (1 - l_discount)) as revenue,
      c_acctbal,
      n_name,
      c_address,
      c_phone,
      c_comment
from
      customer,
      orders,
      lineitem,
      nation
where
      c_custkey = o_custkey
      and l_orderkey = o_orderkey
      and o_orderdate >= date '1993-03-01'
      and o_orderdate < date '1993-03-01' + interval '3' month
      and l_returnflag = 'R'
      and c_nationkey = n_nationkey
group by
      c_custkey,
      c_name,
      c_acctbal,
      c_phone,
      n_name,
      c_address,
      c_comment
order by
      revenue desc
      limit 20;

〇〇 · 发表于 2011-6-12 20:10

pgdb=# \timing
Timing is on.
pgdb=# -- 17c
pgdb=# select
pgdb-#  sum(l_extendedprice) / 7.0 as avg_yearly
pgdb-# from (select l_extendedprice, l_partkey ,l_quantity
pgdb(#  from
pgdb(#  lineitem,
pgdb(#  part
pgdb(#  where
pgdb(#  p_partkey = l_partkey
pgdb(#  and p_brand = 'Brand#23'
pgdb(#  and p_container = 'MED CAN'
pgdb(#  )b
pgdb-#  ,(select l_partkey,0.2 * avg(l_quantity) p2avg_l_quantity
pgdb(#  from
pgdb(#  (select l_partkey,l_quantity from
pgdb(#  lineitem
pgdb(#  where
pgdb(#  l_partkey in (select p_partkey from part where p_brand = 'Brand#23'and p_container = 'MED CAN')
pgdb(#  )a
pgdb(#  group by l_partkey
pgdb(#  )c
pgdb-# where b.l_partkey=c.l_partkey and b.l_quantity <p2avg_l_quantity
pgdb-# ;
   avg_yearly
----------------------
3188455.648571428571
(1 row)

Time: 37718.707 ms

[笔记] 在pg上作tpch测试步骤