我們有一個非常大的表，周圍有總行數，~4 billion每天的吞吐量約為~20-25 million.
下面是示例表定義。
table_name (
id bigint,
user_id bigint,
status smallint,
date timestamp,
.
.
some more columns
);
注意：狀態是一個列舉，可以有 6-7 個可能的值。
下面是我們要優化的查詢：
SELECT *
FROM table_name
WHERE user_id = $user_id
AND status in (1, 2, 3, 6, 7, 10)
ORDER BY date
DESC LIMIT 20;
最初，我們index1在table_name (user_id, status).
由于該索引沒有提供最佳性能。我們也考慮在索引中包含日期。
現在，我們嘗試在表上創建一堆不同的索引，但解釋計劃總是選擇初始索引，即：index1。
以下是我們嘗試的索引：
index2: table_name (user_id, status, date)
index3: table_name (user_id, status, date) where (status = ANY (ARRAY [1, 2, 3, 6, 7, 10]))
index4: table_name (user_id, status) where (status = ANY (ARRAY [1, 2, 3, 6, 7, 10]));
下面是解釋分析輸出：
Limit (cost=27029.07..27029.12 rows=20 width=251) (actual time=32.466..32.470 rows=20 loops=1)
-> Sort (cost=27029.07..27049.17 rows=8041 width=251) (actual time=32.465..32.467 rows=20 loops=1)
Sort Key: id DESC
Sort Method: top-N heapsort Memory: 38kB
-> Index Scan using index1 on table_name wt (cost=0.58..26815.10 rows=8041 width=251) (actual time=0.027..26.991 rows=37362 loops=1)
Index Cond: ((user_id = 111111) AND (status = ANY ('{1,3,2,7,6,10,8}'::integer[])))
Planning Time: 0.320 ms
Execution Time: 32.498 ms
Our database postgres version is: 12.7 and we run vaccuming regularly.
We want to understand why other indexes are not being used for our query.
Also given our use case, Can there be a better way of creating index so that we can serve the query in acceptable response time?
uj5u.com熱心網友回復：
CREATE INDEX table_name_desc_index ON table_name (userid,_date DESC NULLS LAST);
然后嘗試以下操作：
SELECT *
FROM table_name
inner join (values (1),(2),(3),(6),(7),(10)) val(v) ON (table_name.status = v )
WHERE user_id = $user_id
ORDER BY date
DESC LIMIT 20;
uj5u.com熱心網友回復：
您正在選擇，因此由于拖動的列多于索引中的列*，您將無法獲得僅索引掃描。*您展示的那些其他索引的唯一優點（我可以看到）是啟用僅索引掃描，所以如果這不起作用，那么不選擇使用這些索引也就不足為奇了。您可以通過將 更改為僅出現在索引中的列來測驗這個理論，*看看會發生什么。
關于您的索引之一：
(user_id, status, date) where (status = ANY (ARRAY [1, 2, 3, 6, 7, 10]))
這個索引似乎毫無意義。WHERE 子句的好處是它將“狀態”的不同限定值減少到單個值（“真”）。但是然后將“狀態”放入索引主體只會再次將它們分解。更好的索引是：
(user_id, date) where (status = ANY (ARRAY [1, 2, 3, 6, 7, 10]))
這可以跳轉到特定 user_id 的末尾，向后掃描（以實作order by date desc）并在找到 20 行后停止。當您將“身份”作為闖入者時，它會阻止它這樣做。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/442348.html
標籤：sql postgresql postgresql-performance
上一篇：使用pg_dump備份Postgres表是否有包含--jobs選項的解決方法
下一篇：賦值陳述句會導致空指標例外嗎？