postgresql - 使用 LIMIT..OFFSET 时使用奇怪的 PostgreSQL 索引

Question

x86_64-pc-linux-gnu 上的 PostgreSQL 9.6.3，由 gcc (Debian 4.9.2-10) 4.9.2，64 位编译

表格和索引:

create table if not exists orders
(
    id bigserial not null constraint orders_pkey primary key,
    partner_id integer,
    order_id varchar,
    date_created date,
    state_code integer,
    state_date timestamp,
    recipient varchar,
    phone varchar,
);

create index if not exists orders_partner_id_index on orders (partner_id);
create index if not exists orders_order_id_index on orders (order_id);
create index if not exists orders_partner_id_date_created_index on orders (partner_id, date_created);

任务是创建分页/排序/过滤数据。

第一页的查询:

select order_id, date_created, recipient, phone, state_code, state_date
from orders
where partner_id=1 and date_created between '2019-04-01' and '2019-04-30'
order by order_id asc limit 10 offset 0;

查询计划:

QUERY PLAN
"Limit  (cost=19495.48..38990.41 rows=10 width=91)"
"  ->  Index Scan using orders_order_id_index on orders  (cost=0.56..**41186925.66** rows=21127 width=91)"
"        Filter: ((date_created >= '2019-04-01'::date) AND (date_created <= '2019-04-30'::date) AND (partner_id = 1))"

没有使用索引orders_partner_id_date_created_index，所以成本极高!

但是从一些偏移值开始(确切的值不时不同，看起来它取决于总行数)索引开始被使用:

select order_id, date_created, recipient, phone, state_code, state_date
from orders
where partner_id=1 and date_created between '2019-04-01' and '2019-04-30'
order by order_id asc limit 10 offset 40;

计划:

QUERY PLAN
"Limit  (cost=81449.76..81449.79 rows=10 width=91)"
"  ->  Sort  (cost=81449.66..81502.48 rows=21127 width=91)"
"        Sort Key: order_id"
"        ->  Bitmap Heap Scan on orders  (cost=4241.93..80747.84 rows=21127 width=91)"
"              Recheck Cond: ((partner_id = 1) AND (date_created >= '2019-04-01'::date) AND (date_created <= '2019-04-30'::date))"
"              ->  Bitmap Index Scan on orders_partner_id_date_created_index  (cost=0.00..4236.65 rows=21127 width=0)"
"                    Index Cond: ((partner_id = 1) AND (date_created >= '2019-04-01'::date) AND (date_created <= '2019-04-30'::date))"

这是怎么回事？这是强制服务器使用索引的方法吗？

Answer 1

一般答案:

• Postgres 存储一些关于你的表的信息
• 在执行查询之前，规划器根据这些信息准备执行计划
• 在您的情况下，计划者认为对于某些偏移值，此次优计划会更好。请注意，您想要的计划需要按 order_id 对所有选定的行进行排序，而这个“更差”的计划则不需要。我猜想 Postgres 打赌会有很多这样的行用于各种订单，并且只是从最低开始测试一个接一个的订单。

我可以想到两种解决方案:

A) 通过运行为刨床提供更多数据

ANALYZE orders;

( https://www.postgresql.org/docs/9.6/sql-analyze.html )

或者改变收集的统计数据

ALTER TABLE orders SET STATISTCS (...);

( https://www.postgresql.org/docs/9.6/planner-stats.html )

B) 以提示所需索引使用的方式重写查询，如下所示:

WITH
partner_date (partner_id, date_created) AS (
    SELECT  1,
            generate_series('2019-04-01'::date, '2019-04-30'::date, '1 day'::interval)::date
)
SELECT o.order_id, o.date_created, o.recipient, o.phone, o.state_code, o.state_date
FROM   orders o
JOIN   partner_date pd
    ON (o.partner_id, o.date_created) = (pd.partner_id, pd.date_created)
ORDER BY order_id ASC LIMIT 10 OFFSET 0;

或者甚至更好:

WITH
partner_date (partner_id, date_created) AS (
    SELECT  1,
            generate_series('2019-04-01'::date, '2019-04-30'::date, '1 day'::interval)::date
), 
all_data AS (
    SELECT o.order_id, o.date_created, o.recipient, o.phone, o.state_code, o.state_date
    FROM   orders o
    JOIN   partner_date pd
        ON (o.partner_id, o.date_created) = (pd.partner_id, pd.date_created)
)
SELECT *
FROM   all_data
ORDER BY order_id ASC LIMIT 10 OFFSET 0;

免责声明 - 我无法解释为什么第一个查询应该由 Postgres 规划器以其他方式解释，只是认为它可以。另一方面，第二个查询将偏移量/限制与连接分开，如果 Postgres 仍然以“坏”(根据您的基准)方式进行操作，我会感到非常惊讶。