Postgres Pro AXE : Documentation: 1: G.1. TPC-H Benchmark Test : Postgres Professional

G.1. TPC-H Benchmark Test
Prev	Up	Appendix G. Benchmark Test Results	Home	Next

G.1. TPC-H Benchmark Test #

G.1.1. Creating Analytical Tables For the TPC-H Benchmark Test
G.1.2. TPC-H Benchmark Test: Scale Factor 10
G.1.3. TPC-H Benchmark Test: Scale Factor 100

G.1.1. Creating Analytical Tables For the TPC-H Benchmark Test #

Execute the following query:

COPY nation
TO '/home/test/tpch_tables100/nation.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY region
TO '/home/test/tpch_tables100/region.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY part
TO '/home/test/tpch_tables100/part.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY supplier
TO '/home/test/tpch_tables100/supplier.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY partsupp
TO '/home/test/tpch_tables100/partsupp.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY customer
TO '/home/test/tpch_tables100/customer.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY orders
TO '/home/test/tpch_tables100/orders.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

COPY lineitem
TO '/home/test/tpch_tables100/lineitem.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');

CREATE VIEW nation_parquet AS SELECT
r['n_nationkey']::integer AS n_nationkey,
r['n_name']::text AS n_name,0
r['n_regionkey']::integer AS n_regionkey,
r['n_comment']::text AS n_comment FROM
read_parquet('/home/test/tpch_tables100/nation.parquet', binary_as_string => true) r;


CREATE VIEW region_parquet AS SELECT
r['r_regionkey']::integer AS r_regionkey,
r['r_name']::text AS r_name,
r['r_comment']::text AS r_comment FROM
read_parquet('/home/test/tpch_tables100/region.parquet', binary_as_string => true) r;


CREATE VIEW part_parquet AS SELECT
r['p_partkey']::integer AS p_partkey,
r['p_name']::text AS p_name,
r['p_mfgr']::text AS p_mfgr,
r['p_brand']::text AS p_brand,
r['p_type']::text AS p_type,
r['p_size']::integer AS p_size,
r['p_container']::text AS p_container,
r['p_retailprice']::decimal(15,2) AS p_retailprice,
r['p_comment']::text AS p_comment FROM
read_parquet('/home/test/tpch_tables100/part.parquet', binary_as_string => true) r;


CREATE VIEW supplier_parquet AS SELECT
r['s_suppkey']::integer AS s_suppkey,
r['s_name']::text AS s_name,
r['s_address']::text AS s_address,
r['s_nationkey']::integer AS s_nationkey,
r['s_phone']::text AS s_phone,
r['s_acctbal']::decimal(15,2) AS s_acctbal,
r['s_comment']::text AS s_comment FROM
read_parquet('/home/test/tpch_tables100/supplier.parquet', binary_as_string => true) r;


CREATE VIEW nation_parquet AS SELECT
r['n_nationkey']::integer AS n_nationkey,
r['n_name']::text AS n_name,
r['n_regionkey']::integer AS n_regionkey,
r['n_comment']::text AS n_comment FROM
read_parquet('/home/test/tpch_tables100/nation.parquet', binary_as_string => true) r;


CREATE VIEW region_parquet AS SELECT
r['r_regionkey']::integer AS r_regionkey,
r['r_name']::text AS r_name,
r['r_comment']::text AS r_comment FROM
read_parquet('/home/test/tpch_tables100/region.parquet', binary_as_string => true) r;


CREATE VIEW part_parquet AS SELECT
r['p_partkey']::integer AS p_partkey,
r['p_name']::text AS p_name,
r['p_mfgr']::text AS p_mfgr,
r['p_brand']::text AS p_brand,
r['p_type']::text AS p_type,
r['p_size']::integer AS p_size,
r['p_container']::text AS p_container,
r['p_retailprice']::decimal(15,2) AS p_retailprice,
r['p_comment']::text AS p_comment FROM
read_parquet('/home/test/tpch_tables100/part.parquet', binary_as_string => true) r;


CREATE VIEW supplier_parquet as select
r['s_suppkey']::integer as s_suppkey,
r['s_name']::text as s_name,
r['s_address']::text as s_address,
r['s_nationkey']::integer as s_nationkey,
r['s_phone']::text as s_phone,
r['s_acctbal']::decimal(15,2) as s_acctbal,
r['s_comment']::text as s_comment from
read_parquet('/home/test/tpch_tables100/supplier.parquet', binary_as_string => true) r;

CREATE VIEW lineitem_parquet AS SELECT
r['l_orderkey']::integer AS l_orderkey,
r['l_partkey']::integer AS l_partkey,
r['l_suppkey']::integer AS l_suppkey,
r['l_linenumber']::integer AS l_linenumber,
r['l_quantity']::decimal(15,2) AS l_quantity,
r['l_extendedprice']::decimal(15,2) AS l_extendedprice,
r['l_discount']::decimal(15,2) AS l_discount,
r['l_tax']::decimal(15,2) AS l_tax,
r['l_returnflag']::text AS l_returnflag,
r['l_linestatus']::text AS l_linestatus,
r['l_shipdate']::date AS l_shipdate,
r['l_commitdate']::date AS l_commitdate,
r['l_receiptdate']::date AS l_receiptdate,
r['l_shipinstruct']::text AS l_shipinstruct,
r['l_shipmode']::text AS l_shipmode,
r['l_comment']::text AS l_comment FRO
read_parquet('/home/test/tpch_tables100/lineitem.parquet', binary_as_string => true) r;

G.1.2. TPC-H Benchmark Test: Scale Factor 10 #

Table G.1. Test Data

Data	Total size
Analytical tables in the CSV format	12.3 GB
Analytical tables in the Parquet format	2.3 GB

Table G.2. Table Information

Analytical Table	Number of Rows
`customer`	1 500 000
`lineitem`	60 000 000
`nation`	25
`orders`	15 000 000
`part`	2 000 000
`partsupp`	8 000 000
`region`	5
`supplier`	100 000

Table G.3. Test Results

Query	Duration (s)	`user time` (s)	`sys time` (s)	`max RSS` (MB)	`avg RSS` (MB)	`read IO` (MB)
Q1	1.24	4.41	0.49	175.78	119.21	547.41
Q1 — second execution	0.64	3.97	0.13	28.99	19.21	0.00
Q2	0.41	0.35	0.14	137.95	55.49	125.65
Q2 — second execution	0.16	0.65	0.08	87.58	87.58	0.12
Q3	1.16	2.83	0.73	207.54	149.16	859.54
Q3 — second execution	0.43	2.55	0.24	79.77	75.65	0.00
Q4	0.75	1.54	0.38	160.36	96.14	372.92
Q4 — second execution	0.34	1.46	0.14	94.09	94.03	0.00
Q5	1.10	2.71	0.65	150.69	86.55	816.84
Q5 — second execution	0.46	2.58	0.20	61.12	52.95	0.00
Q6	0.70	1.06	0.41	138.09	83.34	429.32
Q6 — second execution	0.23	0.73	0.09	4.45	4.45	0.00
Q7	1.15	2.35	0.73	107.64	56.96	778.34
Q7 — second execution	0.44	2.64	0.25	138.61	92.78	0.00
Q8	1.26	3.29	0.78	111.01	70.97	891.59
Q8 — second execution	0.54	2.48	0.18	132.82	113.96	0.00
Q9	1.56	5.75	1.33	1187.36	844.31	998.59
Q9 — second execution	0.96	5.41	0.84	1016.37	856.13	0.00
Q10	1.29	2.69	0.92	279.62	91.94	897.04
Q10 — second execution	0.53	2.41	0.34	294.97	144.50	0.00
Q11	0.74	0.88	0.14	60.24	27.92	113.06
Q11 — second execution	0.58	0.83	0.08	75.74	69.54	0.00
Q12	0.78	1.64	0.48	115.89	66.10	554.29
Q12 — second execution	0.30	1.61	0.14	58.46	53.79	0.00
Q13	1.05	5.64	0.57	566.08	323.92	384.95
Q13 — second execution	0.81	5.08	0.37	424.62	304.15	0.00
Q14	1.21	1.77	0.85	144.30	91.96	982.34
Q14 — second execution	0.30	1.65	0.22	169.76	169.08	0.00
Q15	1.14	2.21	0.68	116.00	75.72	681.02
Q15 — second execution	0.49	2.43	0.27	130.55	108.68	0.06
Q16	0.35	0.76	0.20	226.43	99.28	105.09
Q16 — second execution	0.22	0.68	0.16	220.67	220.67	0.00
Q17	1.19	2.06	0.58	45.12	3.68	928.13
Q17 — second execution	0.38	1.46	0.14	25.31	12.52	0.00
Q18	0.99	3.22	0.75	854.52	449.22	332.49
Q18 — second execution	0.65	3.20	0.54	817.68	559.04	0.00
Q19	1.35	5.36	0.78	248.18	147.08	1047.49
Q19 — second execution	0.77	4.96	0.25	113.11	111.55	0.00
Q20	1.08	1.53	0.73	94.06	33.74	830.65
Q20 — second execution	0.29	1.48	0.18	82.06	78.86	0.00
Q21	1.72	6.05	0.82	412.80	283.63	742.20
Q21 — second execution	1.14	6.59	0.45	425.94	322.94	0.00
Q22	0.36	0.68	0.17	144.75	63.07	140.62
Q22 — second execution	0.15	0.00	0.00	0.00	0.00	0.00

G.1.3. TPC-H Benchmark Test: Scale Factor 100 #

Table G.4. Test Data

Data	Total size
Analytical tables in the CSV format	112.7 GB
Analytical tables in the Parquet format	24.3 GB
Database	128 GB

Table G.5. Table Information

Analytical Table	Number of Rows
`customer`	15 000 000
`lineitem`	600 000 000
`nation`	25
`orders`	150 000 000
`part`	20 000 000
`partsupp`	80 000 000
`region`	5
`supplier`	1 000 000

Table G.6. Test Results

Query	Duration (s)	`user time` (s)	`sys time` (s)	`max RSS` (MB)	`avg RSS` (MB)	`read IO` (MB)	`write IO` (MB)
Q1	12.92	72.80	4.05	492.73	311.95	6358.24	0.00
Q1 — second execution	9.46	70.33	1.16	409.30	273.64	0.00	0.00
Q2	2.12	6.27	0.96	491.55	361.52	1321.13	0.00
Q2 — second execution	1.03	6.48	0.40	354.27	302.37	0.00	0.00
Q3	11.38	33.43	5.69	934.96	765.60	9683.71	0.00
Q3 — second execution	4.58	31.15	1.31	875.82	686.64	0.00	0.00
Q4	8.10	36.50	5.93	4775.98	3691.67	4396.98	0.00
Q4 — second execution	2.77	17.54	0.86	839.92	641.36	0.00	0.00
Q5	10.74	40.22	5.07	656.58	521.63	9477.89	0.00
Q5 — second execution	5.38	37.76	1.17	645.07	531.99	0.00	0.00
Q6	7.02	12.20	3.64	265.81	207.99	6284.00	0.00
Q6 — second execution	1.89	10.53	0.76	263.79	182.32	0.00	0.00
Q7	11.26	31.34	5.93	1247.71	606.35	9093.37	0.00
Q7 — second execution	4.28	27.75	1.63	1439.95	794.07	0.00	0.00
Q8	12.48	45.23	5.87	909.35	707.38	9528.32	0.00
Q8 — second execution	6.08	42.93	1.57	784.45	639.46	0.00	0.00
Q9	16.47	81.05	12.55	12589.41	10355.32	11261.07	0.00
Q9 — second execution	11.66	77.63	8.09	12392.88	10138.04	0.00	0.00
Q10	11.97	30.71	7.86	2207.39	680.45	10548.94	0.00
Q10 — second execution	4.34	27.91	2.14	1841.71	635.12	0.00	0.00
Q11	1.78	6.22	0.84	209.13	110.15	978.71	0.00
Q11 — second execution	1.04	5.98	0.37	201.26	128.80	0.00	0.00
Q12	6.79	17.32	3.98	326.39	179.29	5353.07	0.00
Q12 — second execution	2.57	15.04	0.92	406.32	248.50	0.00	0.00
Q13	8.29	54.70	4.03	4087.91	2248.88	3647.88	0.00
Q13 — second execution	7.36	55.07	2.86	3989.38	2200.67	0.00	0.00
Q14	12.22	16.23	8.04	1672.34	1471.48	11552.39	0.00
Q14 — second execution	2.51	14.48	1.80	1805.81	1419.27	0.00	0.00
Q15	9.60	25.58	5.90	878.58	453.89	7232.63	0.02
Q15 — second execution	4.11	23.70	2.20	1280.88	789.34	0.06	0.00
Q16	1.47	6.80	1.19	1471.02	803.93	798.05	0.00
Q16 — second execution	1.05	6.62	0.83	1352.21	890.62	0.00	0.00
Q17	11.55	36.66	4.95	323.85	103.95	9784.29	0.00
Q17 — second execution	5.38	35.37	1.19	347.43	274.43	0.00	0.00
Q18	10.20	50.57	7.40	7231.00	3504.42	3183.46	0.00
Q18 — second execution	8.03	48.98	5.96	6903.64	3178.65	0.00	0.00
Q19	12.86	55.66	6.89	1300.25	845.86	11058.48	0.00
Q19 — second execution	7.77	55.12	2.43	1625.37	1393.91	0.00	0.00
Q20	9.83	16.10	5.21	390.94	302.91	8856.13	0.00
Q20 — second execution	2.41	14.04	1.03	374.55	260.89	0.00	0.00
Q21	14.97	66.87	8.54	4101.62	2792.17	7644.21	0.00
Q21 — second execution	10.46	64.93	4.72	3951.81	2257.96	0.00	0.00
Q22	1.97	9.47	0.95	237.94	134.41	1340.10	0.00
Q22 — second execution	1.33	9.05	0.34	243.36	181.38	0.00	0.00

Prev	Up	Next
Appendix G. Benchmark Test Results	Home	G.2. TPC-DS Benchmark Test

epub pdf