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.
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.
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).
The plan described so far is also shown in terms of an SQL optmized query which can be immediately executed
on the underlying DBMS.
Attendees will also graphically compare the structural optmized plan to the one computed by the native optmizer. H-DB allows users to navigate
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:
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.