It seems postgresql is unable to choose correct index in such cases.
(my pg version is 9.3.2)
Let's see example:
create table t1 as select a.a, b.b from generate_series(1, 100) a(a), generate_series(1,500000) b(b);
create index t1_a_idx on t1(a);
create index t1_b_idx on t1(b);
create index t1_a_b_idx on t1(a,b);
create index t1_b_a_idx on t1(b,a);
alter table t1 alter a set statistics 10000;
alter table t1 alter b set statistics 10000;
analyze t1;
test=> explain select count(*) from t1 where a in (1, 9, 17, 26, 35, 41, 50) and b = 333333;
QUERY PLAN
--------------------------------------------------------------------------------------
Aggregate (cost=46.62..46.63 rows=1 width=0)
-> Index Only Scan using t1_a_b_idx on t1 (cost=0.57..46.60 rows=7 width=0)
Index Cond: ((a = ANY ('{1,9,17,26,35,41,50}'::integer[])) AND (b = 333333))
(3 rows)
Rows estimation is exact.
But I think using t1_a_b_idx index is not the best choice.
Let's check:
# drop pg and disc buffers/caches
systemctl stop postgresql.service ; echo 3 >/proc/sys/vm/drop_caches ; systemctl start postgresql.service ; sleep 2
# warm up pg and check the plan
{ echo '\\timing' && echo "explain select count(*) from t1 where a in (1, 9, 17, 26, 35, 41, 50) and b = 333333;" ; } | psql test
# do the benchmark
{ echo '\\timing' && echo "select count(*) from t1 where a in (1, 9, 17, 26, 35, 41, 50) and b = 333333;" ; } | psql test
I have 200-210ms timing for the last query and t1_a_b_idx is used always. I checked several times.
Ok. Now 'drop index t1_a_b_idx;' and check again.
Pg now uses t1_b_a_idx and I have 90-100ms for that control query. This is much better.
I took pageinspect contrib module, learnt btree structure and it is clear for me
why t1_b_a_idx is better. The question is: Is postgresql able to see that?
