sql - PostgreSQL查询的执行时间很慢

我对这个查询做了解释分析，它给出了30ms，但是如果数据更多，我将得到一个执行过期；使用PostgreSQL 10
正常执行：https://explain.depesz.com/s/gSPP
执行缓慢：https://explain.depesz.com/s/bQN2

SELECT inventory_histories.*, order_items.order_id as order_id FROM
"inventory_histories" LEFT JOIN order_items ON (order_items.id =
inventory_histories.reference_id AND inventory_histories.reference_type = 4)
WHERE "inventory_histories"."inventory_id" = 1313 AND
(inventory_histories.location_id = 15) ORDER BY inventory_histories.id DESC
LIMIT 10 OFFSET 0;

索引：

"inventory_histories_pkey" PRIMARY KEY, btree (id)
"inventory_histories_created_at_index" btree (created_at)
"inventory_histories_inventory_id_index" btree (inventory_id)
"inventory_histories_location_id_index" btree (location_id)

最佳答案

对于此查询：

SELECT ih.*, oi.order_id as order_id
FROM "inventory_histories" ih LEFT JOIN
     order_items oi
     ON oi.id = ih.reference_id AND
        ih.reference_type = 4
WHERE ih."inventory_id" = 1313 AND
      ih.location_id = 15
ORDER BY ih.id DESC
LIMIT 10 OFFSET 0;

对于此查询，需要在inventory_histories(inventory_id, location_id, id, reference_id)和order_items(id, reference_type, order_id)上创建复合索引。