Partition elimination in PostgreSQL and Greenplum
Presented by:
Nimish Avarsekar
Nimish Avarsekar is working for Greenplum (fork of PostgreSQL) over a decade now. He is the Product Manager for the Query Performance Acceleration [QPA] and Cluster Management [CM] team. He has worked as a Member of Technical Staff in Greenplum Support assisting customers, driving product escalations and tracking/promoting Greenplum features and improvements. Nimish earned his Bachelor's in Engineering [IT] from Mumbai University in 2006 and Master's in Information Systems from the University of Florida in 2008.
Checkout the slides
PostgreSQL supports partitioning of the tables. Though if they grow large in terms of number of child tables and/or store large amounts of data become inefficient in-terms of query planning and memory usage for execution. Greenplum Database, fork of PostgreSQL, has long supported partitioning tables. With increasing application scenarios and heightened scalability requirements, the original design is becoming less desirable and has generated numerous improvement requests in the field. In particular, the way partitioned tables are currently represented internally prevents certain query optimizations and to some extent query execution from being scalable. For example, in order to generate a query plan, all parts of the table are currently inspected, and partial plans are generated for each of them. Greenplum optimizer introduces a new concept of partition elimination at runtime called Dynamic Partition Elimination [DPE]. This concept eliminates the need to generate large plans during query planning, are memory efficient and perform partition elimination during query execution. This kind of runtime execution was needed in a data-warehousing structure where table partitions can grow up to very large sizes and store large amounts of data. The discussion will also cover differences between the Postgresql Planner and the Greenplum Optimizer specific to Partition handling, to provide insights into how PostgreSQL planner could be enhanced for large partition table use cases.
- Date:
- 2023 February 23 - 10:45
- Duration:
- 45 min
- Room:
- Robusta + Arabica
- Conference:
- PGConf India, 2023
- Language:
- Track:
- Difficulty:
- Medium
- Full Text Search: Capabilities of PostgreSQL and a Comparison with Elasticsearch Using Wikipedia Dataset
- Start Time:
- 2023 February 23 10:45
- Room:
- Grand Victoria 1
- pg_hint_plan - get the right plan without surprises
- Start Time:
- 2023 February 23 10:45
- Room:
- Grand Victoria 2