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

[Koen De Groote <kdg.dev@xxxxxxxxx>]

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.dev@xxxxxxxxx> wrote:

Forgot to mention, this is on Postgres 11.2

On Wed, Sep 8, 2021 at 11:04 PM Koen De Groote <kdg.dev@xxxxxxxxx> 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