Sample Header Ad - 728x90

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