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

And initial setup is wrong. There should be no 'and a002=false' in the
indexes.

On Wed, Sep 8, 2021 at 11:15 PM Koen De Groote <kdg(dot)dev(at)gmail(dot)com> wrote:

> Forgot to mention, this is on Postgres 11.2
>
> On Wed, Sep 8, 2021 at 11:04 PM Koen De Groote <kdg(dot)dev(at)gmail(dot)com> wrote:
>
>> Greetings all.
>>
>> Example table:
>>
>> CREATE TABLE my_table (
>> id serial PRIMARY KEY,
>> a001 BOOLEAN default 't',
>> a002 BOOLEAN default 'f',
>> a003 BOOLEAN default 't',
>> a004 BOOLEAN default 'f'
>> );
>>
>> And these 2 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?
>>
>> Regards,
>> Koen De Groote
>>
>>