PostgreSQL 性能 (col = value or col is NULL)

這個問題涉及 PostgreSQL 9.5 的查詢性能。

表格是：

CREATE TABLE big_table
(
  id integer NOT NULL,
  flag bigint NOT NULL,
  time timestamp with timezone NOT NULL,
  val int,
  primary key (id)
)

考慮以下查詢：

SELECT * 
FROM big_table 
WHERE time > '0666-06-06 00:00:00+00' AND 
     flag & 2 = 2 AND 
     (val = 5 or val IS NULL) 
ORDER by time desc

目標是在不使用聯合的情況下使此查詢性能盡可能好。我使用索引實現了最佳性能：

CREATE INDEX ind 
ON big_table USING btree (time DESC, val)
WHERE (flag & 2::smallint = 2::smallint);

這使得查詢使用索引兩次並使用點陣圖或在它們之間。這仍然比我做不到的要糟糕得多val = ANY('{NULL,5}')。

這使我考慮使用一些特殊的值，例如 -1 而不是 NULL，這將使我能夠val = ANY('{-1,5}')以更好的性能執行全索引掃描。

那麼在這種情況下使用一些特殊值而不是 NULL 更好嗎？或者 PostgreSQL 是否針對您想要在非 NULL 值和 NULL 之間進行“或”的情況進行了一些優化？

我讀了這篇很棒的文章，但沒有看到我剛剛問的沒有使用聯合的解決方案：https ://www.cybertec-postgresql.com/en/avoid-or-for-better-performance/

我也在 Stack Overflow 和這裡搜尋了這樣的問題，但相關問題要麼不談論性能，要麼不談論非 NULL 和 NULL。這個類似的問題（以及它收到的答案）談到了 SQL Server 而不是 PostgreSQL：Best way to write SQL Query that checks a column for non-NULL value or NULL

我還考慮過合併以擺脫 null，但讀到它會使性能變得更糟。

該表有超過 400 萬條記錄。其中只有 56 條記錄在 val 列中有 NULL。

更新：原始解釋：

"Limit  (cost=1112.32..1112.32 rows=2 width=519)"
"  ->  Sort  (cost=1112.32..1112.32 rows=2 width=519)"
"        Sort Key: time DESC"
"        ->  Bitmap Heap Scan on big_table  (cost=1104.29..1112.31 rows=2 width=519)"
"              Recheck Cond: (((time > '0666-06-06 00:00:00+00'::timestamp with time zone) AND (val = 3) AND ((flag & '2'::smallint) = '2'::smallint)) OR ((time > '0666-06-06 00:00:00+00'::timestamp with time zone) AND (val IS NULL) AND ((flag & '2'::smallint) = '2'::smallint)))"
"              ->  BitmapOr  (cost=1104.29..1104.29 rows=2 width=0)"
"                    ->  Bitmap Index Scan on ind  (cost=0.00..552.14 rows=1 width=0)"
"                          Index Cond: ((time > '0666-06-06 00:00:00+00'::timestamp with time zone) AND (val = 3))"
"                    ->  Bitmap Index Scan on ind  (cost=0.00..552.14 rows=1 width=0)"
"                          Index Cond: ((time > '0666-06-06 00:00:00+00'::timestamp with time zone) AND (val IS NULL))"

更改(val = 3 or val IS NULL)為coalesce(val,-1) = ANY('{3,-1}')創建索引後說明：

CREATE INDEX ind2 ON big_table USING btree (coalesce(val,-1), time DESC) WHERE (flag & 2::smallint = 2::smallint);

是：

"Limit  (cost=863.45..863.50 rows=20 width=519)"
"  ->  Sort  (cost=863.45..863.99 rows=216 width=519)"
"        Sort Key: time DESC"
"        ->  Bitmap Heap Scan on big_table  (cost=11.08..857.71 rows=216 width=519)"
"              Recheck Cond: ((COALESCE(val, '-1'::integer) = ANY ('{3,-1}'::integer[])) AND (time > '0666-06-06 00:00:00+00'::timestamp with time zone) AND ((flag & '2'::smallint) = '2'::smallint))"
"              ->  Bitmap Index Scan on ind2  (cost=0.00..11.03 rows=216 width=0)"
"                    Index Cond: ((COALESCE(val, '-1'::integer) = ANY ('{3,-1}'::integer[])) AND (time > '0666-06-06 00:00:00+00'::timestamp with time zone))"

執行原始查詢但更改原始索引中 time 和 val 列的順序會給出以下更好的解釋計劃：

"Limit  (cost=16.92..16.92 rows=2 width=519)"
"  ->  Sort  (cost=16.92..16.92 rows=2 width=519)"
"        Sort Key: time DESC"
"        ->  Bitmap Heap Scan on big_table  (cost=8.89..16.91 rows=2 width=519)"
"              Recheck Cond: (((val = 3) AND (time > '0666-06-06 00:00:00+00'::timestamp with time zone) AND ((flag & '2'::smallint) = '2'::smallint)) OR ((val IS NULL) AND (time > '0666-06-06 00:00:00+00'::timestamp with time zone) AND ((flag & '2'::smallint) = '2'::smallint)))"
"              ->  BitmapOr  (cost=8.89..8.89 rows=2 width=0)"
"                    ->  Bitmap Index Scan on ind  (cost=0.00..4.44 rows=1 width=0)"
"                          Index Cond: ((val = 3) AND (time > '0666-06-06 00:00:00+00'::timestamp with time zone))"
"                    ->  Bitmap Index Scan on ind  (cost=0.00..4.44 rows=1 width=0)"
"                          Index Cond: ((val IS NULL) AND (time > '0666-06-06 00:00:00+00'::timestamp with time zone))"

實際上，現在執行合併查詢實際上會減慢查詢速度（即使列顛倒但在索引中替換為合併）！

用代理值替換NULL可能會或可能不會有一點幫助。NULL處理索引的成本要高一些，但另一方面，它的儲存通常較小，這也會影響性能。

我預計會產生更大的影響：翻轉索引表達式的順序：

CREATE INDEX ON big_table (val, time DESC)
WHERE flag & 2::smallint = 2::smallint;

經驗法則：首先是相等的索引 - 然後是范圍。看：

• 多列索引和性能

讓您感到安慰的是：val = ANY('{-1,5}')被燒毀為 . 的語法簡寫(val = -1 OR val = 5)，這幾乎不比(val IS NULL OR val = 5). （更重要的因素是NULLvs.的行數-1- 如果您只是替換NULL為-1.，那麼在您的情況下也是如此）。

還可以考慮升級到目前版本的 Postgres。9.5 變老了，對大表進行了各種性能改進。

要僅返回少數列，僅索引掃描可能是一種優化，但您需要根據註釋返回 21 列中的大部分。

為了節省每行 8 個字節不必要地失去對齊填充，重新排序展示表的列，如下所示：

CREATE TABLE big_table (
  flag bigint NOT NULL,
  time timestamp with timezone NOT NULL,
  id integer PRIMARY KEY,
  val int
);

總體而言，越小越快。現在，這顯然只是一個展示表，但相同的原則適用於您的實際表。看：

• 為讀取性能配置 PostgreSQL
• 在 PostgreSQL 中計算和節省空間

種類

對於單個val，Postgres 可以直接從索引中返回預排序的數據。但是對於多個值，它必須合併相同數量的排序子集（一個排序集用於val IS NULL，另一個用於val = 5您的範例），因此在索引訪問之上的另一個排序步驟是不可避免的。來自索引的預排序集仍然可以使其更便宜 - 在任何情況下您都需要排序索引元組。實際的查詢計劃還取決於所選的索引訪問方法。從索引掃描（或僅索引掃描）返回預先排序的數據很簡單。對於點陣圖索引掃描來說，沒有那麼多。

特殊情況：很少的NULL值，一直使用

由於您僅提到了數百萬行的少數 NULL 值，因此我將為該特殊情況添加一個定制的索引：

CREATE INDEX ON big_table (time DESC)
WHERE flag & 2::smallint = 2::smallint
AND   val IS NULL;

甚至可能將所有其他感興趣的列附加到這個非常小的特殊索引中，以獲得僅索引掃描。（取決於先決條件。）在 Postgres 11 或更高版本中，使用該INCLUDE子句的真正覆蓋索引更是如此。此索引和其他索引的結果合併到一個BitmapOr節點中，就像您現在看到的來自同一索引的多個子集一樣。Postgres 對特殊情況有精確的估計，無論特殊情況是 NULL 還是 -1 或其他什麼，它都變得完全無關緊要。（並不是一開始就那​​麼重要。）請參閱：

• Postgres 可以對這個帶有連接表的查詢使用僅索引掃描嗎？
• 未使用但影響查詢的索引

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