Optimizing queries involving partitioned tables
Ashutosh Bapat has been working with database internal for over fifteen years. He is working with PostgreSQL for more than a decade now. He has contributed features like partition-wise join, advanced partition matching for partition-wise join, partition-wise aggregate, join and aggregate push-down for postgres_fdw. His proposal for atomic commit for FDW is being discussed on hackers. Recently he joined 2ndQuadrant where he is working with BDR and logical replication.
No video of the event yet, sorry!
Introduced in PostgreSQL 10, partitioned tables are becoming a main stream feature of PostgreSQL. When combined with FDW esp. postgres_fdw, these two technologies form a base for distributed query processing in a sharded setup.
Partitioned table distribute the data into smaller chunks or when coupled with FDW they distribute the data across multiple servers. PostgreSQL's query optimizer takes advantage of this distribution to speed up queries involving partitioned table. It plans queries so that the basic SQL operations like join, aggregation, filtering, sorting are broken down to work at the level of partitions instead of partitioned table. This allows these operations to be performed in-memory, in parallel if the partitions are local. If partitions are foreign, the operations can be pushed down to the foreign server. Either way the goal is to make query efficient.
In this talk we will cover these optimizations and reflect on what problems lie ahead. But PostgreSQL's planner requires a lot of memory when dealing with a large number of partitions. To a large extent this is because PostgreSQL's partitions are really tables, which require to be planned separately. The solution is to use efficient data structures or to reduce the amount of partition specific handling we need to do. We will close the discussion with some thoughts on those line.
- 2020 November 19 14:50 CST
- 40 min
- Virtual - English Sub-Conference B
- CHINA 2020 And PGConf.Asia 2020