On Wednesday, September 8, 2021, Koen De Groote <
kdg.dev@gmail.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.
