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

From: Koen De Groote <kdg(dot)dev(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: How does postgres behave if several indexes have (nearly) identical conditions?
Date: 2021-09-08 21:04:36
Message-ID: CAGbX52HAf7EdeNNrHGMGLFdftiY6-jUnayRaEt5a5cR2E56sRQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Koen De Groote 2021-09-08 21:15:00 Re: How does postgres behave if several indexes have (nearly) identical conditions?
Previous Message Miles Elam 2021-09-08 15:49:22 Re: Logical Replication to Older Version