Image 0
Image 1
Image 2
H-DB

A hybrid optimizer for SQL queries

Welcome to H-DB Website

H-DB is an SQL query optimizer that combines classical quantitative optimization techniques with structural decomposition methods. The system is based on hypertree decompositions techniques and provides support to optimizing SQL queries with arbitrary output variables, aggregate operators, ORDER BY statements, and nested queries. H-DB can be put on top of any existing database management system supporting JDBC technology, by transparently interacting/replacing its standard query optimization module. To push benefits of structural methods to a maximum, it provides an extension to PostgreSQL.

CIKM 2011 Demo Preview

Attendees will graphically evaluate execution plans for standard TPCH benchmark queries by using the explaining capabilities of H-DB.
As a preview example we consider here a possible scenario for the query TPHC_3 on a 2gb database.

Hypertree decomposition of query TPCH 3


We set the upper bound of the decomposition width to 2 and we allow use of pruning optmization techniques. Attendees can configure these setting and run the explainer using the Simulation Menu.

A first interesting explain output consists in the hypertree and in the correspondent physical execution plan (respectively depicted on the rightside and on the leftside of the picture below). Attendees will easily check that query TPHC_3 is acyclic by looking at the retrieved hypertree (whose width is 1). Thus, the query can be efficiently solved by using the given physical plan, which will be eventually executed in a bottom up fashion starting from the leaves. In details, the executor will first perform a semijoin of orders and customer and then will compute a semi join of the resulting view and lineitem to produce the final result. As shown, H-DB computes a renaming of query attributes wich are mapped to an uniform space of variables. Attendees can use the report panel just below the decomposition to see how original attributes are represented. Additionally, the panel gives details about catalog data used to compute the actual plan (i.e., selectivity of attributes, cardinality of relations).

Hypertree decomposition of query TPCH 3


The plan described so far is also shown in terms of an SQL optmized query which can be immediately executed on the underlying DBMS.

Optmized SQL plan of query TPCH 3


Attendees will also graphically compare the structural optmized plan to the one computed by the native optmizer. H-DB allows users to navigate

Comparing native plan (left side) to the structural aware one (right side)


plan trees and shows statistics about execution costs of physical operators. This view is extremely helpful to quickly evaluate the impact of structural operators on plan quality. As a relevant example here, demo attendees will see that the overall costs of custom semijoin operators are smaller than the ones of standard join operator (mainly related to the number of produced output tuples which is bounded when a semi join is executed). As a result, the global cost of the whole optmized plan is smaller than the one produced by the native optmizer (we have 1482585 for the former, 1837586 for the latter). A graphical recap of the computed costs is also available as shown:

Comparing native plan (left side) to the structural aware one (right side)


Attendees will also compute different optimized plans by changing the decomposition width on the fly. H-DB offers a comphensive
summarization panel which can be used to select the best plan for a query. Additionally, one can compare plans on different databases, varying the cardinality of relations and the selectivity of attributes.