優化兩個大表的查詢

我的系統中有一個非常重要的查詢，由於表上的大量數據而執行時間過長。我是一名初級 DBA，我需要為此得到最好的優化。每個表大約有 8000 萬行。

表格是：

tb_pd:

  Column            |  Type   | Modifiers | Storage | Stats target | Description 
---------------------+---------+-----------+---------+--------------+-------------
pd_id               | integer | not null  | plain   |              | 
st_id               | integer |           | plain   |              | 
status_id           | integer |           | plain   |              | 
next_execution_date | bigint  |           | plain   |              | 
priority            | integer |           | plain   |              | 
is_active           | integer |           | plain   |              | 
Indexes:
   "pk_pd" PRIMARY KEY, btree (pd_id)
   "idx_pd_order" btree (priority, next_execution_date)
   "idx_pd_where" btree (status_id, next_execution_date, is_active)
Foreign-key constraints:
   "fk_st" FOREIGN KEY (st_id) REFERENCES tb_st(st_id)

tb_st:

Column |          Type          | Modifiers | Storage  | Stats target | Description 
--------+------------------------+-----------+----------+--------------+-------------
st_id  | integer                | not null  | plain    |              | 
st     | character varying(500) |           | extended |              | 
Indexes:
   "pk_st" PRIMARY KEY, btree (st_id)
Referenced by:
   TABLE "tb_pd" CONSTRAINT "fk_st" FOREIGN KEY (st_id) REFERENCES tb_st(st_id)

我的查詢是：

select s.st                                               
from tb_pd p inner join
    tb_st s on p.st_id = s.st_id
where p.status_id = 1 and
     p.next_execution_date < 1401402110830 and
     p.is_active = 1
order by priority, next_execution_date
limit 20000;

使用我擁有的索引，我得到的最好的是：

Limit  (cost=1.14..263388.65 rows=20000 width=45)
  ->  Nested Loop  (cost=1.14..456016201.43 rows=34627017 width=45)
        ->  Index Scan using idx_pd_order on tb_pd p  (cost=0.57..161388942.77 rows=34627017 width=16)
              Index Cond: (next_execution_date < 1401402110830::bigint)
              Filter: ((status_id = 1) AND (is_active = 1))
        ->  Index Scan using pk_st on tb_st s  (cost=0.57..8.50 rows=1 width=37)
              Index Cond: (st_id = p.st_id)

我不能很好地理解解釋，但它沒有使用idx_pd_where過濾 where 子句。具有 where 子句中使用的idx_pd_where所有列。

有關數據的更多資訊：

status_id是 95% = 1

is_active是 90% = 1

next_execution_date以毫秒為單位並且變化很大。比較的值是執行的時刻（以毫秒為單位的目前時間）

我應該為每個過濾列創建單獨的索引還是使用任何不同類型的索引？也許在 DBMS 上進行一些配置？

這是一個棘手的問題。您的主要條件是 on next_execution_date，但輸出首先排序priority。條件只佔很小的一部分status_id。is_active

更好的索引

您的索引idx_pd_order沒有太大幫助，因為過濾多列索引的非前導列效率不高。Postgres 正在使用它——仍然比順序掃描好很多。此處的詳細資訊：

複合索引是否也適用於第一個欄位的查詢？

idx_pd_where可能是一個更好的選擇，但也不是一個好選擇。前導列status_id根本沒有選擇性，只會使索引膨脹。尾隨的列也是如此is_active。並且priority不在索引中，必須從表中獲取，因此無法進行僅索引掃描。

我建議從這個部分的、多列的索引開始。（但請繼續閱讀！）

CREATE INDEX idx_pd_covering ON tb_pd (next_execution_date, priority, st_id)
WHERE  status_id = 1 AND is_active = 1

• 因為我們只對包含其他行的行感興趣，status_id = 1並is_active = 1立即從索引中排除其他行。大小確實很重要。
• 剩下的（關鍵）條件是 on next_execution_date，它必須在索引中排在第一位。
• priority並且st_id僅附加用於可能的僅索引掃描（Postgres 9.2+）。如果不這樣做，請從索引中刪除列以使其更小。

特殊難度

我們現在可以idx_pd_covering用來快速找到符合條件的行，不幸的是我們必須查看所有符合條件的行來收集最高的行priority。正如查詢計劃所揭示的，Postgres 估計要處理34627017 行。對 3500 萬行進行排序會花費很大。這就是我一開始提到的棘手部分。為了展示我在說什麼，請在帶有和不帶有in 的情況下執行EXPLAIN您的查詢： priority``ORDER BY

SELECT s.st                                               
FROM   tb_pd p
JOIN   tb_st s USING (st_id)
WHERE  p.status_id = 1
AND    p.is_active = 1
AND    p.next_execution_date < 1401402110830
ORDER  BY priority, next_execution_date
LIMIT  20000;

那是您的查詢，格式僅略微簡化。你應該看到一個巨大的差異。

解決方案

解決方案取決於 的不同值的數量priority。由於缺乏資訊和展示目的，我將假設只有三個。優先級**1和。2****3**

使用微不足道的不同優先級值，有一個簡單的解決方案。創建三個部分索引。所有這些仍然小於您目前的索引idx_pd_order或idx_pd_where（您可能不再需要）。

CREATE INDEX idx_pd_covering_p1 ON tb_pd (next_execution_date, st_id)
WHERE  priority = 1 AND status_id = 1 AND is_active = 1;

CREATE INDEX idx_pd_covering_p2 ON tb_pd (next_execution_date, st_id)
WHERE  priority = 2 AND status_id = 1 AND is_active = 1;

CREATE INDEX idx_pd_covering_p3 ON tb_pd (next_execution_date, st_id)
WHERE  priority = 3 AND status_id = 1 AND is_active = 1;

使用此查詢：

SELECT s.st
FROM  (
  (
  SELECT st_id
  FROM   tb_pd
  WHERE  status_id = 1
  AND    is_active = 1
  AND    **priority = 1**
  AND    next_execution_date < 1401402110830
  ORDER  BY next_execution_date
  )
  UNION ALL
  (
  SELECT st_id
  FROM   tb_pd
  WHERE  status_id = 1
  AND    is_active = 1
  AND    **priority = 2**
  AND    next_execution_date < 1401402110830
  ORDER  BY next_execution_date
  )
  UNION ALL
  (
  ...
  AND    **priority = 3**
  ...
  )
  LIMIT  20000
  ) p
JOIN   tb_st s USING (st_id);

這應該是炸藥。

• 嚴格來說，如果外部查詢中沒有附加ORDER BY子句，則無法保證最終順序。在目前實現中，只要外部查詢如此簡單，就保留內部查詢的順序。可以肯定的是，您可以立即加入（可能會慢一些）：

)
SELECT s.st
FROM   tb_pd p
JOIN   tb_st s USING (st_id)
WHERE  p.status_id = 1
AND    p.is_active = 1
AND    p.priority  = 1
AND    p.next_execution_date < 1401402110830
ORDER  BY p.next_execution_date
)
UNION ALL
(
...
)
LIMIT  20000;

.. 或者在外部查詢中繼續priority並next_execution_date再次訂購（絕對可以肯定），這可能會更慢。

• 所有括號都是必需的！相關答案。
• 這個查詢只是從上面部分索引的頂部讀取元組，根本不需要排序步驟。所有行都預先排序，高效啟動。
• UNION ALL``ORDER BY一旦獲取了頂層中請求的行數，沒有最終結果的查詢就會停止LIMIT。因此，如果最高優先級中有足夠的行，UNION ALL則永遠不會執行查詢的後續分支。這樣，只有較小的部分索引必須被觸及。
• JOIN到tb_st以後，應該會更有效率。
• 同樣，該列st_id僅附加到索引中，希望進行僅索引掃描。如果這對您有用，那麼整個查詢甚至根本不會觸及表格tb_pd。

任意數量的不同priority值的通用解決方案

我們之前已經解決了這個問題。有一個完整的配方可以自動創建部分索引和函式.. 工作原理：

空間索引可以幫助“按限制範圍排序”查詢

優化表

由於您正在嘗試優化性能並且您的表格很大，因此我建議您稍微改變表格的佈局tb_pd：

  Column            |  Type
---------------------+--------
pd_id               | integer
st_id               | integer
next_execution_date | bigint
priority            | **integer**  -- or smallint? -- or "char"?
status_id           | **smallint** -- or "char"
is_active           | **boolean**

這在磁碟上每行佔用 52 個字節，而您目前的設計需要 60 個字節。指數也獲利。詳細資訊：

配置 PostgreSQL 以提高讀取性能

當然，所有關於性能優化的基本建議也適用。

關於"char"：

類型"char"（注意引號）的不同之處char(1)在於它只使用一個字節的儲存空間。它在系統目錄內部用作簡單的列舉類型。

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