Partitioning of tables based on value ranges provides a powerful mechanism to organize tables in database systems. In the context of data warehousing and large-scale data analysis partitioned tables are of particular interest as the nature of queries favors scanning large swaths of data. In this scenario, eliminating partitions from a query plan that contain data not relevant to answering a given query can represent substantial performance improvements. Dealing with partitioned tables in query optimization has attracted significant attention recently, yet, a number of challenges unique to Massively Parallel Processing (MPP) databases and their distributed nature remain unresolved. In this paper, we present optimization techniques for queries over partitioned tables as implemented in Pivotal Greenplum Database. We present a concise and unified representation for partitioned tables and devise optimization techniques to generate query plans that can defer decisions on accessing certain partitions to query run-time. We demonstrate, the resulting query plans distinctly outperform conventional query plans in a variety of scenarios.
[1]
Vivek R. Narasayya,et al.
Integrating vertical and horizontal partitioning into automated physical database design
,
2004,
SIGMOD '04.
[2]
Goetz Graefe.
The Cascades Framework for Query Optimization
,
1995,
IEEE Data Eng. Bull..
[3]
Herodotos Herodotou,et al.
Query optimization techniques for partitioned tables
,
2011,
SIGMOD '11.
[4]
Chun Zhang,et al.
Automating physical database design in a parallel database
,
2002,
SIGMOD '02.
[5]
George C. Caragea,et al.
Orca: a modular query optimizer architecture for big data
,
2014,
SIGMOD Conference.