postgresql - Postgres解释计划对于具有不同参数的相同查询是不同的

Question

当我使用不同的参数值vsexplain运行以下查询时，我不知道为什么查询会产生不同的结果：sender_id = 2015sender_id = 2016

• 与sender_id = 2015：

EXPLAIN SELECT
    *
FROM
    shipment.shipments shipment0_
WHERE
    (shipment0_.status IN (0 ,1 ,2))
    AND shipment0_.sender_id = 2015
ORDER by shipment0_.id DESC
LIMIT 10;

Limit  (cost=0.43..4.92 rows=10 width=11006)
  ->  Index Scan Backward using shipments__id_pk on shipments shipment0_  (cost=0.43..446315.18 rows=993372 width=11006)
        Filter: ((sender_id = 2015) AND (status = ANY ('{0,1,2}'::bigint[])))

• 与sender_id = 2016：

EXPLAIN SELECT
    *
FROM
    shipment.shipments shipment0_
WHERE
    (shipment0_.status IN (0 ,1 ,2))
    AND shipment0_.sender_id = 2016
ORDER by shipment0_.id DESC
LIMIT 10;

Limit  (cost=0.43..16.33 rows=10 width=11006)
  ->  Index Scan using idx_sender_id_id on shipments shipment0_  (cost=0.43..155621.11 rows=97858 width=11006)
        Index Cond: (sender_id = 2016)
        Filter: (status = ANY ('{0,1,2}'::bigint[]))

在shipment.shipments表中有 1M 记录sender_id = 2015：

select count(*) from shipment.shipments where sender_id = 2015; -- 1000000 records
select count(*) from shipment.shipments where sender_id = 2016; -- 90000 records

以下是表格的 DDL shipment.shipments：

CREATE TABLE shipment.shipments (
    id bigserial NOT NULL,
    shp_date varchar(50) NULL,
    shp_time varchar(50) NULL,
    sender_id int8 NULL,
    status int8 NULL,
    pickup_order_id int8 NULL,
    CONSTRAINT shipments__id_pk PRIMARY KEY (id),
    CONSTRAINT shipments__pickup_orders_id_fk FOREIGN KEY (pickup_order_id) REFERENCES shipment.pickup_orders(id)
);
CREATE INDEX idx_sender_id_id ON shipment.shipments USING btree (sender_id, id DESC);
CREATE INDEX shipments__sender_id_idx ON shipment.shipments USING btree (sender_id);

查询sender_id = 2016运行速度非常快，order by limit但sender_id = 2015速度非常慢并且不使用 index idx_sender_id_id。你能帮我解释一下吗？以及如何提高查询速度sender_id = 2015。太感谢了！

标签： postgresqlindexing