Обсуждение: How does postgres behave if several indexes have (nearly) identical conditions?

Поиск
Список
Период
Сортировка

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

От
Koen De Groote
Дата:
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

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

От
Koen De Groote
Дата:
Forgot to mention, this is on Postgres 11.2

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

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

От
"David G. Johnston"
Дата:


On Wednesday, September 8, 2021, Koen De Groote <kdg.dev@gmail.com> wrote:


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.

Neither…since neither partial index condition is present in the where clause of the query.

David J.
 

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

От
"David G. Johnston"
Дата:
On Wednesday, September 8, 2021, Koen De Groote <kdg.dev@gmail.com> wrote:
Forgot to mention, this is on Postgres 11.2

You should stop worrying about performance and  indexes and instead focus on system stability and security - i.e., upgrade to a supported version.

David J.

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

От
Koen De Groote
Дата:
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@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.dev@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

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

От
"David G. Johnston"
Дата:
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.