Teradata_PostgreSQL

Document Sample
Teradata_PostgreSQL Powered By Docstoc
					Some TPC-H queries on Teradata
       and PostgreSQL



 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 2GB RAM,   •Twin Xeon processors with 2GB
proprietary parallel storage      RAM, RAID 5
system
                                  • Linux Kernel version 2.4.18-10smp
• Windows 2000 Advanced Server
                                  • PostgreSQL v7.2.1
• Teradata v4.1.2
                    TPC-H Schema
Part                                             Order
(200K rows)                                      (1500K rows)


              Partsupp            Lineitem
              (800K rows)         (6000K rows)


Supplier                                         Customer
(10K rows)                                       (150K rows)
                       Nation
                       (24 rows)


                       Region                    • A typical manufacturing
                       (5 rows)                   concern database
                                                 • Approximately 1GB 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'                                                                       Supplier
  AND ps_supplycost = (
    SELECT
       min(ps_supplycost)
                                                                               Partsupp   Part
    FROM                                                                                  (p_size=15)
       partsupp, supplier, nation, region       Region                                    (p_type=„%BRASS‟)
                                                (r_name=„EUROPE‟)
    WHERE
       p_partkey = ps_partkey
       AND s_suppkey = ps_suppkey
                                                       Region         Nation               Part
       AND s_nationkey = n_nationkey
       AND n_regionkey = r_regionkey
       AND r_name = 'EUROPE'
  )
ORDER BY                                                            Teradata Query Plan
  s_acctbal desc, n_name, s_name, p_partkey;
                                                               • Analysis of query execution
                                                               plans of both systems
                                           Part                • Added indexes (B-Tree
                                           (p_size=15)
                                           (p_type=„%BRASS‟)   indexes on all)

                                Partsupp
                                                               • Rewrote the query using
                                                  Part         “explicit join” clause
                          Supplier                             • Reduced query time from 40
                                                               minutes to 2 seconds
Region                 Nation
(r_name=„EUROPE‟)



      Region



                    PostgreSQL Query Plan
           Query execution times
Teradata        t1              t2               t3          After adding
                                                             secondary index on
   Q6         31 sec          25 sec           26 sec        n_nationkey on
                                                             supplier table
   Q2         11 sec          10 sec            1 sec

   Q3      2 min 15sec      2 min 11 sec     1 min 16 sec
                                                              After Collect
                                                              statistics



Postgre        Initial          After            After
                             “ANALYZE”          adding
 SQL                                           indexes
   Q6          33 sec           32 sec          30 sec

   Q2       40 m 19 sec       35 min 4 sec       2 sec

   Q3       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
  – PostgreSQL Documentation
  – Teradata Documentation
  – Database Tuning, Dennis Shasha

				
DOCUMENT INFO