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....@gmail.com> wrote: > Forgot to mention, this is on Postgres 11.2 > > On Wed, Sep 8, 2021 at 11:04 PM Koen De Groote <kdg....@gmail.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 >> >>
