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

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
>
>