Some TPCH queries on Teradata and Postgre SQL

Motivation • Usage of real Database Systems • Gain some experience in database tuning

TPC-H Schema Part (200 K rows) Order (1500 K rows) Partsupp (800 K rows)

TPC-H Query 2 SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment FROM part,

• Analysis of query execution plans of both systems Part (p_size=15) (p_type=‘%BRASS’) Partsupp

Query execution times Teradata t 1 t 2 t 3 Q 6 31 sec

• Conclusion: – Query plans are very useful in database tuning – Parallel

Slides: 7

Download presentation

Some TPC-H queries on Teradata and Postgre. SQL Project Partners: • Amreek Singh (02329025) • Chetan Vaity (02329901)

Motivation • Usage of real Database Systems • Gain some experience in database tuning • Work with Teradata machine in SIT Test Setup • Twin processors with 2 GB RAM, proprietary parallel storage system • Windows 2000 Advanced Server • Teradata v 4. 1. 2 • Twin Xeon processors with 2 GB RAM, RAID 5 • Linux Kernel version 2. 4. 18 -10 smp • Postgre. SQL v 7. 2. 1

TPC-H Schema Part (200 K rows) Order (1500 K rows) Partsupp (800 K rows) Lineitem (6000 K rows) Supplier (10 K rows) Customer (150 K rows) Nation (24 rows) Region (5 rows) • A typical manufacturing concern database • Approximately 1 GB of data

TPC-H Query 2 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 = 15 AND p_type like '%BRASS' AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'EUROPE' AND ps_supplycost = ( SELECT min(ps_supplycost) FROM Region partsupp, supplier, nation, region (r_name=‘EUROPE’) WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey Region AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'EUROPE' ) ORDER BY s_acctbal desc, n_name, s_name, p_partkey; Supplier Partsupp Nation Part (p_size=15) (p_type=‘%BRASS’) Part Teradata Query Plan

• Analysis of query execution plans of both systems Part (p_size=15) (p_type=‘%BRASS’) Partsupp Part Supplier Region Nation (r_name=‘EUROPE’) Region Postgre. SQL Query Plan • Added indexes (B-Tree indexes on all) • Rewrote the query using “explicit join” clause • Reduced query time from 40 minutes to 2 seconds

Query execution times Teradata t 1 t 2 t 3 Q 6 31 sec 25 sec 26 sec Q 2 11 sec 10 sec 1 sec Q 3 2 min 15 sec 2 min 11 sec 1 min 16 sec After adding secondary index on n_nationkey on supplier table After Collect statistics Postgre SQL Initial After “ANALYZE” After adding indexes Q 6 33 sec 32 sec 30 sec Q 2 40 m 19 sec 35 min 4 sec 2 sec Q 3 11 min 43 sec 11 min 32 sec 1 min 11 sec

• Conclusion: – Query plans are very useful in database tuning – Parallel architecture under full DBMS control performs • Bibliography – http: //www. tpc. org – Postgre. SQL Documentation – Teradata Documentation – Database Tuning, Dennis Shasha