sql - 我需要什么索引？

Question

我对这个查询的性能有疑问。如果我删除 Order by section 一切正常。但我真的想要它。我尝试使用许多索引但没有任何结果。你能帮我吗？

SELECT *
     FROM "refuel_request" AS "refuel_request"
     LEFT OUTER JOIN "user" AS "user" ON "refuel_request"."user_id" = "user"."user_id"
     LEFT OUTER JOIN "bill_qr" AS "bill_qr" ON "refuel_request"."bill_qr_id" = "bill_qr"."bill_qr_id"
     LEFT OUTER JOIN "car" AS "order.car" ON "refuel_request"."car_id" = "order.car"."car_id" 
     LEFT OUTER JOIN "refuel_request_status" AS "refuel_request_status" ON "refuel_request"."refuel_request_status_id" = "refuel_request_status"."refuel_request_status_id"
     WHERE
      refuel_request."refuel_request_status_id" IN ( '1', '2', '3') 
     ORDER BY "refuel_request".created_at desc
     LIMIT 10 

There is explain of this query

EXPLAIN (ANALYZE, BUFFERS)

主键和/或外键

1. pk_refuel_request_id
2. refuel_request_bill_qr_id_fkey
3. refuel_request_user_id_fkey

Answer 1

所有外连接表都与refuel_request 1:n 相关。这意味着您的查询正在寻找状态为 1 到 3 的最后十个创建的加油请求。

您是外部连接表，因为并非每个 reful_request 都与一个 user、一个 bill_qr、一个 car 和 status。或者你错误地加入了外部。无论如何，没有一个连接会改变检索到的行数；每个加油请求仍然是一行。为了连接其他表的行，DBMS 只需要它们的主键索引。不用担心。

我们唯一需要关心的是尽快找到您感兴趣的状态的顶部 reful_request 行。

使用仅包含相关状态数据的部分索引。你索引的列是 created_at 列，以便立即获得前 10。

CREATE INDEX idx ON refuel_request (created_at DESC)
                 WHERE refuel_request_status_id IN (1, 2, 3);

这里解释了部分索引:https://www.postgresql.org/docs/current/indexes-partial.html