Conference Schedule - PGCon 2022

Performance improvement of filtering foreign tables using subquery for PostgreSQL

Date: 2022-05-26
Time: 09:00 - 09:45
Room: Stream 3
Level: Intermediate

There are many cases where filtering is performed on one table based on the search results of another table.
In this case, it is possible to perform such queries by using JOIN clause. However, if each of tables is a foreign table, all data will be fetched from remote servers. As a result, because unnecessary data is also retrieved, so there is a problem that the expected performance cannot be obtained. Therefore, I made a prototype to generate a query plan that can execute the subquery first when the join condition is specified by the IN clause using the subquery expression, instead of generating a simple joion plan such as hash join. I confirmed the effect of speeding up to several tens of times. In this session, I will explain specific problems, solutions and changes, performance, and future works.


Shigeo Hirose