Re: How does postgres behave if several indexes have (nearly) identical conditions?

On Wednesday, September 8, 2021, Koen De Groote <kdg(dot)dev(at)gmail(dot)com> wrote:

> And initial setup is wrong. There should be no 'and a002=false' in the
> indexes.
>
>
>>> create index index_001 on my_table using btree (a001,a002,a003) where
>>> a001=true and a002=false;
>>>
>>> create index index_002 on my_table using btree (a003) where a001=true
>>> and a002=false;
>>>
>>> Now take this query:
>>>
>>> select * from my_table where a001=true;
>>>
>>> Which index will postgres pick? I'm wondering how postgres goes about
>>> picking an index to consider.
>>>
>>> And if it will consider others if the analysis of the first says a
>>> seqscan would be better than the index it first considered?
>>>
>>

Still probably neither since the sequential scan is likely the better
choice (it depends on the number of true rows compared to all rows). It
will have to look at the statistical data for both but given that the
three-column one is strictly worse than the single column version (because
the indexed columns don’t contribute anything worthwhile) it will mostly
likely be a choice between a sequential scan and the index 002. Though if
002 has lots of bloat compared to index 001 the later may beat it out - but
that just means your system needs index maintenance performed.

David J.