文本列比較等於 where 子句但不選擇匹配行

查詢生產數據庫中的表時遇到問題。一個文本列將與我們在 where 子句中過濾的字元串進行比較，但 postgres 不會選擇該行。（我們在 postgres 11.11 上）我們的表設置如下：

(PROD)=> \d names;
                          Table "public.names"
       Column        |            Type             | Collation | Nullable | Default
----------------------+-----------------------------+-----------+----------+---------
name                 | text                        |           | not null |
processed_name       | text                        |           | not null |
name_index           | integer                     |           | not null |
when_created         | timestamp without time zone |           | not null |
Indexes:
   "names_pkey" PRIMARY KEY, btree (name, processed_name)
   "names_name_index_key" UNIQUE CONSTRAINT, btree (name_index)
   "ix_names_name" btree (name)
   "ix_names_processed_name" btree (processed_name)

當我們處理名稱列表時，我們檢查它們是否已經在表中，以防止重複添加和違反主鍵約束。

但是，在一個名稱“Сергей Иванович МЕНЯЙЛО”上，查看該名稱是否已存在的查詢返回一個空集

，我希望返回具有相同名稱的行。但是，當我們嘗試在表中插入行時，我們會遇到主鍵衝突

以下是一些可以更好地解釋問題的查詢

(PROD)=> SELECT name_index, 
   name, 
   name = 'Сергей Иванович МЕНЯЙЛО' names_compare_equal 
FROM names where name_index = 75128;
     name_index      |          name           | names_compare_equal
----------------------+-------------------------+---------------------
               75128 | Сергей Иванович МЕНЯЙЛО | t
(1 row)

但是，在名稱列上進行過濾不會選擇任何行。

2021-05-24 20:37:41 UTC
(PROD)=> SELECT name_index, 
   name, 
   name = 'Сергей Иванович МЕНЯЙЛО' 
   names_compare_equal 
FROM names 
WHERE name = 'Сергей Иванович МЕНЯЙЛО';

      name_index     | name | names_compare_equal
----------------------+------+---------------------
(0 rows)

因此，如果我們嘗試插入行，我們會遇到主鍵衝突：

(PROD)>=> INSERT INTO names (name_index, name, processed_name, when_created) 
 VALUES (89266, 'Сергей Иванович МЕНЯЙЛО', lower('Сергей Иванович МЕНЯЙЛО'), now());

ERROR:  duplicate key value violates unique constraint "names_pkey"
DETAIL:  Key (name, processed_name)=(Сергей Иванович МЕНЯЙЛО, сергей иванович меняйло) already exists.

更重要的是，如果我根據行的雜湊進行查詢，我會得到正確的結果：

(PROD)=> SELECT name_index, 
   name, 
   name = 'Сергей Иванович МЕНЯЙЛО' names_compare_equal 
FROM names 
WHERE md5(name) = md5('Сергей Иванович МЕНЯЙЛО');

     name_index      |          name           | names_compare_equal
----------------------+-------------------------+---------------------
               75128 | Сергей Иванович МЕНЯЙЛО | t
(1 row)

這只發生在我們的生產數據庫上 - 它具有以下編碼設置

     Name      |     Owner      | Encoding |   Collate   |    Ctype    |
----------------+----------------+----------+-------------+-------------+
PROD DB        | PROD DB OWNER  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

這對我來說非常莫名其妙，所以關於下一步要檢查什麼的想法會很有幫助

損壞的索引將是這裡的主要嫌疑人。測試：

SELECT * FROM names WHERE name || '' = 'Сергей Иванович МЕНЯЙЛО';

該表達式name || ''不能使用任何索引，因此您會獲得順序掃描。如果該查詢找到您的條目，則您的診斷結果是：索引損壞。可能是 just (name)，但由於多個索引符合條件，請重新檢查EXPLAIN. （這可能不是 PK，因為它仍然會在您的測試中引發獨特的違規行為，但它也可能已損壞……）

Postgres 11.11的發行說明中有一條註釋：

…請參閱下面的第二個更改日誌項目，其中描述了升級後重新索引索引可能是可取的情況。

或者，您的底層作業系統中的語言環境可能已更新？相同的修復：重新索引。

索引損壞還有其他原因，但唯一的其他常見原因是硬體問題。這應該立即觸發更嚴厲的措施，從備份開始。

重新創建受影響的索引。您可以使用REINDEX：

REINDEX INDEX ix_names_name;

如果您需要允許對錶的並發訪問，請使用非阻塞（但速度較慢）CONCURRENTLY：

REINDEX INDEX ix_names_name CONCURRENTLY;

如果有理由相信問題可能是系統性的，請重新創建表上的所有索引：

REINDEX TABLE names;

或整個數據庫：

REINDEX DATABASE name_of_current_database;

如果進行更大的清理，我會建議一個沒有並發訪問的維護視窗。還有很多maintenance_work_mem。

除此之外，您有兩列的這些索引，name並且processed_name：

"names_pkey" PRIMARY KEY, btree (name, processed_name)
"ix_names_name" btree (name)
"ix_names_processed_name" btree (processed_name)

上的 PK 索引(name, processed_name)可以用於所有可以使用附加索引的內容(name)。僅當列相當大時，該附加索引才可能有用processed_name-在這種情況下，我會考慮從更有效的 PK 開始。看：

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

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