索引不與 = ANY() 一起使用，但與 IN 一起使用

表t有兩個索引：

create table t (a int, b int);
create type int_pair as (a int, b int);
create index t_row_idx on t (((a,b)::int_pair));
create index t_a_b_idx on t (a,b);

insert into t (a,b)
select i, i
from generate_series(1, 100000) g(i)
;

運算符不使用索引ANY：

explain analyze
select *
from t
where (a,b) = any(array[(1,1),(1,2)])
;
                                           QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
Seq Scan on t  (cost=0.00..1693.00 rows=1000 width=8) (actual time=0.042..126.789 rows=1 loops=1)
  Filter: (ROW(a, b) = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
  Rows Removed by Filter: 99999
Planning time: 0.122 ms
Execution time: 126.836 ms

但其中之一與IN運算符一起使用：

explain analyze
select *
from t
where (a,b) in ((1,1),(1,2))
;
                                                   QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
Index Only Scan using t_a_b_idx on t  (cost=0.29..8.32 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=1)
  Index Cond: (a = 1)
  Filter: ((b = 1) OR (b = 2))
  Heap Fetches: 1
Planning time: 0.161 ms
Execution time: 0.066 ms

如果記錄被轉換為正確的類型，它將使用記錄索引：

explain analyze
select *
from t
where (a,b)::int_pair = any(array[row(1,1),row(1,2)])
;
                                                 QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
Index Scan using t_row_idx on t  (cost=0.42..12.87 rows=2 width=8) (actual time=0.106..0.126 rows=1 loops=1)
  Index Cond: (ROW(a, b)::int_pair = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
Planning time: 0.208 ms
Execution time: 0.203 ms

為什麼規劃器不為操作員使用非記錄索引，ANY因為它為IN操作員使用它？

在內部，有兩種不同形式的IN，也有兩種不同形式的ANY構造。

每一個，取一個set，等同於另一個，並且也導致與可以使用普通索引expr IN (<set>)的查詢計劃相同的查詢計劃。expr = ANY(<set>)細節：

• PostgreSQL 中的 IN 與 ANY 運算符

因此，以下兩個查詢是等效的，並且都可以使用普通索引（如果您試圖讓查詢使用索引t_a_b_idx，這也可以是解決方案）：

EXPLAIN ANALYZE
SELECT *
FROM t
WHERE (a,b) = ANY(VALUES (1,1),(1,2));

要麼：

...
WHERE (a,b) IN (VALUES (1,1),(1,2));

兩者相同：

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.33..16.71 rows=1 width=8) (actual time=0.101..0.101 rows=0 loops=1)
   ->  Unique  (cost=0.04..0.05 rows=2 width=8) (actual time=0.068..0.070 rows=2 loops=1)
         ->  Sort  (cost=0.04..0.04 rows=2 width=8) (actual time=0.067..0.068 rows=2 loops=1)
               Sort Key: "*VALUES*".column1, "*VALUES*".column2
               Sort Method: quicksort  Memory: 25kB
               ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=8) (actual time=0.005..0.005 rows=2 loops=1)
   ->  Index Only Scan using t_plain_idx on t  (cost=0.29..8.32 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=2)
         Index Cond: ((a = "*VALUES*".column1) AND (b = "*VALUES*".column2))
         Heap Fetches: 0
 Planning time: 4.080 ms
 Execution time: 0.202 ms

但是，這不能輕易地傳遞給函式，因為 Postgres 中沒有“表變數”。這導致了開始這個話題的問題：

• 將多個值集或數組傳遞給函式

該問題有多種解決方法。一個是我在那裡添加的替代答案。其他一些：

• 將記錄變數中的值插入表中
• 在 PL/pgSQL 中聲明表類型的變數

---

每個的第二種形式是不同的：ANY接受一個實際的數組，而IN接受一個逗號分隔的值列表。

這對輸入輸入有不同的影響。正如我們在EXPLAIN問題的輸出中看到的那樣，這種形式：

WHERE (a,b) = ANY(ARRAY[(1,1),(1,2)]);

被視為以下的簡寫：

ROW(a, b) = ANY (ARRAY[ROW(1, 1), ROW(1, 2)])

並比較實際的 ROW 值。Postgres 目前還不夠聰明，無法看到復合類型上的索引t_row_idx是否適用。它也沒有意識到簡單索引t_a_b_idx也應該適用。

顯式轉換有助於克服這種缺乏智能的問題：

WHERE (a,b)::int_pair = ANY(ARRAY[(1,1),(1,2)]::int_pair[]);

轉換正確的操作數 ( ::int_pair[]) 是可選的（儘管為了性能和避免歧義更可取）。一旦左操作數具有眾所周知的類型，右操作數就會從“匿名記錄”強制轉換為匹配類型。只有這樣，才能明確定義運算符。Postgres 根據運算符和左操作數選擇適用的索引。對於定義 aCOMMUTATOR的許多運算符，查詢計劃器可以翻轉操作數以將索引表達式帶到左側。但這對於構造是不可能的ANY。

有關的：

• 有沒有辦法有效地索引包含正則表達式模式的文本列？

 WHERE (a,b) IN ((1,1),(1,2));

.. 值被視為元素，Postgres 能夠比較單個整數值，正如我們在EXPLAIN輸出中再次看到的那樣：

Filter: ((b = 1) OR (b = 2))

因此 Postgres 發現t_a_b_idx可以使用簡單索引。

---

因此，對於範例中的特定情況，將有另一種解決方案：由於範例中的自定義復合類型int_pair恰好等同於表t本身的行類型，我們可以簡化：

CREATE INDEX t_row_idx2 ON t ((t.*));

更短的等效語法：

CREATE INDEX t_row_idx2 ON t ((t));

但第一種變體更安全。如果應該存在同名的列，則第二個變體將解析為該列。

然後此查詢將使用索引而無需任何更顯式的強制轉換：

EXPLAIN ANALYZE
SELECT *
FROM   t
WHERE  t = ANY(ARRAY[(1,1),(1,2)]);

                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=40.59..496.08 rows=1000 width=8) (actual time=0.19
1..0.191 rows=0 loops=1)
   Recheck Cond: (t.* = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
   ->  Bitmap Index Scan on t_row_idx2  (cost=0.00..40.34 rows=1000 width=0) (actual time=0.188..0.188 rows=0 loops=1)
         Index Cond: (t.* = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
 Planning time: 2.575 ms
 Execution time: 0.267 ms

但是典型的案例將無法利用隱式存在的表行類型。

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