PostgreSQL 錯誤地使用主鍵索引進行最小/最大查詢

我有一個結構類似於此的表：

CREATE TABLE employees (
   id bigserial NOT NULL,
   name_id uuid NOT NULL,
   department uuid NOT NULL,
   details text NULL,
   deleted bool NOT NULL DEFAULT false,
   CONSTRAINT employees_pk PRIMARY KEY (id)
);

CREATE INDEX employees_department_and_id_index ON employees USING btree (department, id);

我需要找到id給定的最高值department，查詢很簡單：

select max(id) from employees
where department = 'some-uuid';

當我查詢員工總數相對較少的部門時，查詢按預期執行，僅使用索引掃描employees_department_and_id_index：

explain analyze select max(id) from employees
where department = '7291e1de-7870-4d68-889e-693e5731fcfb';

Result  (cost=4.58..4.59 rows=1 width=8) (actual time=0.722..0.722 rows=1 loops=1)
 InitPlan 1 (returns $0)
   ->  Limit  (cost=0.56..4.58 rows=1 width=8) (actual time=0.719..0.719 rows=0 loops=1)
         ->  Index Only Scan Backward using employees_department_and_id_index on employees  (cost=0.56..26738.12 rows=6661 width=8) (actual time=0.719..0.719 rows=0 loops=1)
               Index Cond: ((department = '7291e1de-7870-4d68-889e-693e5731fcfb'::uuid) AND (id IS NOT NULL))
               Heap Fetches: 0
Planning Time: 0.111 ms
Execution Time: 0.740 ms

但是，當條件包含大量佔用department時，執行計劃會意外使用employees_pk：

explain analyze select max(id) from employees
where department = 'deadbeef-deaf-feed-dead-beefdeadbeef';

Result  (cost=2.92..2.93 rows=1 width=8) (actual time=190780.059..190780.060 rows=1 loops=1)
 InitPlan 1 (returns $0)
   ->  Limit  (cost=0.56..2.92 rows=1 width=8) (actual time=190780.053..190780.055 rows=1 loops=1)
         ->  Index Scan Backward using employees_pk on employees  (cost=0.56..2257557.69 rows=959468 width=8) (actual time=190780.052..190780.052 rows=1 loops=1)
               Index Cond: (id IS NOT NULL)
               Filter: (department = 'deadbeef-deaf-feed-dead-beefdeadbeef'::uuid)
               Rows Removed by Filter: 50000000
Planning Time: 0.102 ms
Execution Time: 190780.082 ms

請注意執行此類查詢需要多長時間。現在，為了強制使用另一個索引，我刪除了主鍵並再次執行了這個查詢：

ALTER TABLE employees DROP CONSTRAINT employees_pk;
explain analyze select max(id) from employees
where department = 'deadbeef-deaf-feed-dead-beefdeadbeef';

Result  (cost=3.07..3.08 rows=1 width=8) (actual time=1.029..1.030 rows=1 loops=1)
 InitPlan 1 (returns $0)
   ->  Limit  (cost=0.56..3.07 rows=1 width=8) (actual time=1.026..1.027 rows=1 loops=1)
         ->  Index Only Scan Backward using employees_department_and_id_index on employees  (cost=0.56..2407872.31 rows=959468 width=8) (actual time=1.025..1.025 rows=1 loops=1)
               Index Cond: ((department = 'deadbeef-deaf-feed-dead-beefdeadbeef'::uuid) AND (id IS NOT NULL))
               Heap Fetches: 1
Planning Time: 0.094 ms
Execution Time: 1.047 ms

這一次，執行速度快了幾個數量級，這清楚地表明規劃器選擇了錯誤的主鍵索引。

當它們都存在時，可以做些什麼來強制使用正確的索引？這樣analyze做沒有幫助，也嘗試替換max並order by id desc limit 1不會改變計劃。

這甚至可以在具有這樣數據的干淨數據庫上重現 - 我們創建佈局，其中包含一些小部門，然後是一個大部門，然後是更多更小的部門：

create extension if not exists "uuid-ossp";

insert into employees (name_id, department)
select uuid_generate_v4(), dep.d
from 
   (select uuid_generate_v4() as d from generate_series(1, 1000)) as dep,
   (select generate_series(1, 5000)) as a;

insert into employees (name_id, department)
select uuid_generate_v4(), 'deadbeef-deaf-feed-dead-beefdeadbeef'
from generate_series(1, 1000000);

insert into employees (name_id, department)
select uuid_generate_v4(), dep.d
from 
   (select uuid_generate_v4() as d from generate_series(1, 100)) as dep,
   (select generate_series(1, 500000)) as a;

analyze employees;

我在具有 100GB SSD 儲存和預設參數組的 AWS RDS 實例類型 db.m5.large 上的 PostgreSQL 11.6、11.8 和 12.3 上對其進行了測試，都給出了相似的結果。預先感謝您提供有關如何修改查詢、索引或配置參數的任何提示。

TL;DR： PostgreSQL 不使用 sane index for min/max ofid但更喜歡使用主鍵索引來查找表數據的一半，這沒有意義。

如果我完全按照您的步驟操作，在填充表格之前創建索引，我可以重現這一點。但是，如果我在填充表後創建索引，我將無法重現它。那是因為在填充期間存在的索引（當它沒有按順序填充時，主鍵的方式）變得有些臃腫。這種膨脹並不多，但足以將計劃者推到邊緣去選擇另一個計劃。該索引的 REINDEX 應該足以修復它。

如果這對您來說還不夠穩定，您可以通過創建一個 indexON employees (department ,(id+0));並使用max(id+0). PostgreSQL 不將 +0 辨識為標識操作，因此認為它不能滿足它的 in index 僅包括普通的“id”，但可以使用 id+0 上的索引。

根本問題是 PostgreSQL 不理解表中行順序的強模式。由於它確實知道大約 1/56 的表有department = 'deadbeef-deaf-feed-dead-beefdeadbeef'，它認為它會在僅查看 56 行後找到第一個範例，然後可以停止。它還認為所有 56 行都將位於同一個表頁中（因為它確實理解“id”和行順序之間的關係），因此它認為不需要額外的 IO 來查看它們。但是，知道根本問題目前並不能為您提供解決方法，因此您只能採用一種或另一種解決方法。

另一種將其輕輕推向正確方向的方法是對您的桌子進行 VACUUM。將頁面設置為全部可見將有助於估計僅索引掃描（實際上是快速掃描），但無助於估計普通索引掃描（實際上是慢速掃描）。

引用自：https://dba.stackexchange.com/questions/269531