Postgresql

Postgres 9.4.4 查詢需要永遠

  • December 15, 2017

我們在 CentOS 6.5 上執行 Postgres 9.4.4 並且有一個已經工作多年的 SELECT 查詢，但在我們從 9.2 升級後停止工作並掛起（花了一段時間才注意到它，所以我不知道是不是在我們升級或不升級後立即）。

SELECT id || ':' || group_number AS uniq_id
FROM   table_one
WHERE  id || ':' || group_number NOT IN (
  SELECT id || ':' || group_number
  FROM table_two
  )
AND    id NOT IN (
  SELECT id
  FROM table_three
  WHERE timestamp > NOW() - INTERVAL '30 days' 
  AND client_id > 0
  );

在所有表id中都是一個整數，但儲存為character varying (15)（遺留系統）。group_number儲存為smallint.

table_two 的子查詢返回大約 250 萬條記錄。子查詢table_three返回大約 2,500 條記錄。如果單獨執行，兩者都會在大約 1 秒內返回。但是添加任一查詢（或兩者）作為子查詢會導致查詢無限期掛起（幾天，如果我們讓它執行）。

我在網上看到其他人遇到同樣的問題（使用時查詢不返回NOT IN）。NOT IN似乎是一個直截了當的子查詢。

我們擁有大量硬體（384 GB RAM、Xeon 64 核、16 磁碟 15k RPM RAID 10）。

  1. 為什麼會這樣？（即，這是 Postgres 中一個主要的持續錯誤嗎？）
  2. 同時如何修復/調試它？

以下是結果EXPLAIN：

QUERY PLAN
Index Only Scan using table_one_id_pk on table_one  (cost=19690.90..64045129699.10 rows=370064 width=9)
 Filter: ((NOT (hashed SubPlan 2)) AND (NOT (SubPlan 1)))
 SubPlan 2
   ->  Bitmap Heap Scan on table_three  (cost=2446.92..19686.74 rows=8159 width=7)
         Recheck Cond: (("timestamp" > (now() - '30 days'::interval)) AND (client_id > 0))
         ->  BitmapAnd  (cost=2446.92..2446.92 rows=8159 width=0)
               ->  Bitmap Index Scan on table_one_timestamp_idx  (cost=0.00..1040.00 rows=79941 width=0)
                     Index Cond: ("timestamp" > (now() - '30 days'::interval))
               ->  Bitmap Index Scan on fki_table_three_client_id  (cost=0.00..1406.05 rows=107978 width=0)
                     Index Cond: (client_id > 0)
 SubPlan 1
   ->  Materialize  (cost=0.00..84813.75 rows=3436959 width=9)
         ->  Seq Scan on table_two  (cost=0.00..64593.79 rows=3436959 width=9)

我的設置來自postgresql.conf

max_connections = 200
shared_buffers = 24GB
temp_buffers = 8MB
work_mem = 96MB
maintenance_work_mem = 1GB
cpu_tuple_cost = 0.0030
cpu_index_tuple_cost = 0.0010
cpu_operator_cost = 0.0005
effective_cache_size = 128GB
from_collapse_limit = 4
join_collapse_limit = 4

更新

我使用以下方法來調整work_mem這個查詢：

BEGIN;
SET work_mem = '256MB';
-- query --
SET work_mem = default;
COMMIT;

使用NOT IN在 5 - 8 秒內返回（而從不使用work_mem = 96MB）。

使用LEFT JOIN在 13 - 14 秒內返回（與 24 秒相比work_mem = 96MB）。

所以看起來問題出在work_mem，而 usingLEFT JOIN只是一種解決方法。然而，真正的問題是 Postgres 使用work_mem = 96MB.

使用 RAID 10 中的 16 x 15k SAS 驅動器，我們的 I/O 速度非常快，因此即使是磁碟，查詢也應該返回，只是慢了一點。

更新 2

以下是 EXPLAIN ANALYZE 在 LEFT JOIN 方法上的結果：

   QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Anti Join  (cost=27318.56..351160.97 rows=728325 width=9) (actual time=9553.378..21247.202 rows=7 loops=1)
  ->  Hash Anti Join  (cost=27318.47..176945.69 rows=1501249 width=9) (actual time=511.578..5479.549 rows=1478438 loops=1)
        Hash Cond: ((t1.id)::text = (t3.id)::text)
        ->  Seq Scan on table_one t1  (cost=0.00..143842.21 rows=1593403 width=9) (actual time=0.026..4369.804 rows=1485291 loops=1)
        ->  Hash  (cost=27289.76..27289.76 rows=8203 width=7) (actual time=511.518..511.518 rows=1286 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 51kB
              ->  Bitmap Heap Scan on table_three t3  (cost=1518.79..27289.76 rows=8203 width=7) (actual time=125.379..510.998 rows=1286 loops=1)
                    Recheck Cond: (client_id > 0)
                    Filter: ("timestamp" > (now() - '30 days'::interval))
                    Rows Removed by Filter: 104626
                    Heap Blocks: exact=16093
                    ->  Bitmap Index Scan on fki_table_three_client_id  (cost=0.00..1518.38 rows=108195 width=0) (actual time=121.633..121.633 rows=122976 loops=1)
                          Index Cond: (client_id > 0)
  ->  Index Only Scan using t_table_two_id2_idx on table_two t2  (cost=0.09..0.14 rows=1 width=9) (actual time=0.010..0.010 rows=1 loops=1478438)
        Index Cond: ((id = (t1.id)::text) AND (group_number = t1.group_number))
        Heap Fetches: 143348
Planning time: 30.527 ms
Execution time: 21247.541 ms
(18 rows)

Time: 23697.256 ms

在這裡，它們適用於 NOT EXISTS 方法：

   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Anti Join  (cost=27318.56..351160.97 rows=728325 width=9) (actual time=5117.110..14061.838 rows=7 loops=1)
  ->  Hash Anti Join  (cost=27318.47..176945.69 rows=1501249 width=9) (actual time=146.779..1254.400 rows=1478439 loops=1)
        Hash Cond: ((t1.id)::text = (t3.id)::text)
        ->  Seq Scan on table_one t1  (cost=0.00..143842.21 rows=1593403 width=9) (actual time=0.007..591.383 rows=1485291 loops=1)
        ->  Hash  (cost=27289.76..27289.76 rows=8203 width=7) (actual time=146.758..146.758 rows=1285 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 51kB
              ->  Bitmap Heap Scan on table_three t3  (cost=1518.79..27289.76 rows=8203 width=7) (actual time=17.586..146.330 rows=1285 loops=1)
                    Recheck Cond: (client_id > 0)
                    Filter: ("timestamp" > (now() - '30 days'::interval))
                    Rows Removed by Filter: 104627
                    Heap Blocks: exact=16093
                    ->  Bitmap Index Scan on fki_table_one_client_id  (cost=0.00..1518.38 rows=108195 width=0) (actual time=14.415..14.415 rows=122976 loops=1)
                          Index Cond: (client_id > 0)
  ->  Index Only Scan using t_table_two_id2_idx on table_two t2  (cost=0.09..0.14 rows=1 width=9) (actual time=0.008..0.008 rows=1 loops=1478439)
        Index Cond: ((id = (t1.id)::text) AND (group_number = t1.group_number))
        Heap Fetches: 143348
Planning time: 2.155 ms
Execution time: 14062.014 ms
(18 rows)

Time: 14065.573 ms

假設您按照@a_horse 的評論檢查了wiki 頁面中的常見嫌疑人。

另請參閱解決點陣圖索引掃描和work_mem.

詢問

這個重寫的查詢應該更快：

SELECT id || ':' || group_number AS uniq_id
   -- id::text || ':' || group_number AS uniq_id  -- with integer
FROM   table_one t1
WHERE  NOT EXISTS ( 
  SELECT 1
  FROM   table_two t2
  WHERE  t2.id = t1.id
  AND    t2.group_number = t1.group_number
  ) 
AND NOT EXISTS (
  SELECT 1
  FROM   table_three t3
  WHERE  t3.timestamp > NOW() - interval '30 days' 
  AND    t3.client_id > 0
  AND    t3.id = t1.id
  );
  • 最重要的問題是比較table_oneand之間的連接字元串table_two，這通常比必要的成本更高，特別是不可搜尋。
  • 將整數儲存為字元串是昂貴的廢話。你似乎意識到了這一點。integer盡可能轉換為。如果您在 varchar 列中只有有效的整數id，您需要做的就是：
ALTER TABLE table_one ALTER COLUMN id TYPE integer USING id::int;

對於table_two.

  • NOT IN兩邊都有一個NULL 值的陷阱。這就是為什麼**NOT EXISTS**幾乎總是更好。（通常在此之上表現更好。）

索引

無論哪種方式，性能的關鍵是匹配索引。

確保在和上有多列索引：table_one``table_two

CREATE INDEX t1_foo_idx ON table_one (id, group_number)
CREATE INDEX t2_foo_idx ON table_two (id, group_number)

可能允許僅索引掃描。

使用integer而不是varchar，這些將更小，更高效，但是：

我建議在以下位置使用部分多列索引table_three：

CREATE INDEX t3_foo_idx ON table_three (timestamp, id)
WHERE  client_id > 0
AND    timestamp > '2015-06-07 0:0';

有用性會隨著時間的推移而惡化。在適當的時候重新創建具有增加的下限的索引 - 這會在表上使用排他鎖，因此請考慮CREATE INDEX CONCURRENTLY. 詳細解釋：

您需要匹配查詢中的（更新的）索引條件。即使看起來多餘，也要添加條件。喜歡：

...
AND NOT EXISTS (
  SELECT 1
  FROM   table_three t3
  WHERE  t3.timestamp > NOW() - interval '30 days' 
  **AND t3 timestamp > '2015-06-07 0:0'**  -- match index condition
  AND    t3.client_id > 0
  AND    t3.id = t1.id
  );

您可以在部分索引和查詢中將函式用作偽常量並自動執行該過程。此相關答案的最後一章：

SET LOCAL

就像您發現自己一樣，work_mem如果查詢需要那麼多 RAM，則在本地增加查詢會有所幫助。考慮SET LOCAL：

有了所有建議的改進，您可能不需要再增加work_mem了。

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

相關問答

Postgresql

固定寬度的行會提高 PostgreSQL 的讀取性能嗎？

  • March 29, 2021
檢視問答
Postgresql

Postgres 正在執行順序掃描而不是索引掃描

  • April 7, 2019
檢視問答
Postgresql

count(*) 在子查詢中的表現

  • June 29, 2018
檢視問答
Postgresql

從 CTE 獲取聚合數據

  • May 7, 2017
檢視問答
Postgresql

如何創建要由查詢選擇的索引

  • February 2, 2016
檢視問答
Postgresql

優化查詢以獲取 x 在另一個表的值對之間的行

  • January 27, 2016
檢視問答