Performance improvement of filtering foreign tables using subquery for PostgreSQL - Shigeo Hirose

Improve PostgreSQL performance by up to 50 times by optimizing filtering of foreign tables using subqueries, with a proposed solution and real-world testing results.

Key takeaways

The speaker’s goal is to improve the performance of filtering foreign tables using subqueries in PostgreSQL.
The problem is that current PostgreSQL does not execute subqueries efficiently, leading to slow performance.
The speaker proposes a solution to modify PostgreSQL to execute subqueries first and then use the results to filter the foreign tables.
The solution involves five steps: confirming the query, rewriting the query to use the subquery results, creating a plan based on the rewritten query, executing the plan, and returning the results to the user.
The speaker tested the solution using two data sources, each with a large amount of data, and found that the solution improved performance by up to 50 times.
The speaker also discusses the limitations of the current PostgreSQL implementation and the need for future improvements.
The speaker concludes by summarizing the main points of the talk and inviting feedback from the audience.

Performance improvement of filtering foreign tables using subquery for PostgreSQL - Shigeo Hirose

More talks