Index not used with = ANY() but used with IN
27
votes
1
answer
33795
views
Table
t
has two indexes:
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)
;
No index is used with the ANY
operator:
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
But one of them is used with the IN
operator:
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
It uses the record index if the record is cast to the correct type:
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
Why doesn't the planner use the non record index for the ANY
operator as it uses it for the IN
operator?
Asked by Clodoaldo
(1145 rep)
Jan 6, 2016, 05:48 PM
Last activity: Apr 26, 2025, 03:43 PM
Last activity: Apr 26, 2025, 03:43 PM