Optimizing PostgreSQL Queries for Flight Data Retrieval at Qunar

The author reports that a query selecting f.flight_no from tables btm m, bft f, and ptm i with several join conditions takes about 70 ms, even though the involved indexes appear normal.

By simplifying the query to select flight_no from bft where tm_id is in a static list (e.g., (1334090,1334091,1334093)), the execution time drops to roughly 0.17 ms, demonstrating the potential speed gain.

Further experiments show that using a sub‑query with an equality condition ( tm_id = (select item_id ...)) yields a fast runtime of about 0.055 ms, whereas using IN (select item_id ...) results in a slower nested‑loop plan with a runtime near 0.09 ms.

The final solution aggregates the relevant item_id values into an array and joins on that array:

select f.flight_no from btm m, bft f, (select array_agg(item_id) team_ids from ptm where product_id=3683170 and type=2 and status=1) i where m.id = f.traffic_main_id and m.id = any(i.team_ids);

This approach achieves a runtime of about 0.104 ms (≈2.8 ms total), effectively leveraging index scans.

The key insight is to rewrite the condition so that the planner can treat the comparison as an array operation, allowing it to use the existing index on bft.tm_id efficiently.

Author: He Weiping, a search and database researcher at Qunar, former Yahoo China search engineer, and translator of the PostgreSQL Chinese manual and Programming Perl 3rd edition.