The next wave in BI

Topic: TPC-H fun with Greenplum

Date: 12/02/2010

By: Alin Dobra

Subject: Re: Some hints


If I would work for GP, I would be really worried about the TPC-H Q1 performance (even though you are 20 times faster than Postgres). Q1 is probably the most hated query in TPC-H since no DB trick helps (short of pre-computing the answers); the performance depends only on the ability to perform computation -- evaluate expressions. This is precisely why you should be worried about your results since GP does not do very well on Q1.

The MonetDB people have a paper in CIDR 2005 (MonetDB/X100: Hyper-Pipelining Query Execution) that points out exactly what the problem is in most databases: very poor pipelining performance. Running a little interpreter for every evaluation of an expression is bound to be very inefficient and it kills the branch predictions that are the soul of the pipelining. The new engine that MonetDB people use, X100, gets within a factor of 2 of hand-hacked code so no wonder in the test that started this discussion MonetDB runs in 5.5s and GP in 7 minutes.

Unfortunately, there is no easy fix in GP for this problem since the execution engine has to be rewritten. The MonetDB people took this hard decision in 2005 but for them it was an easy decision to make since they had an academic project. One solution is probably some form of just-in-time compilation. That would cut some of the inefficiency but not solve the problem completely since Postgres (and probably GP) is still an one-tuple-at-the-time engine. Peak performance can be obtained only with no interpretation and blocks of tuples at a time (preferably millions). In your particular setup, there is probably a 10x factor speedup in actual running time for Q1 (not more due to the I/O).

A last comment. Performance on computational tasks is important in databases since databases should not be used as a fancy data scanner. You want to perform the computation in the database not in middleware since shipping the data is never efficient.