Postgres not hitting index when expected

Question

Consider the following table:

CREATE TABLE PROJECT (
    PROJECT_ID                      UUID PRIMARY KEY DEFAULT UUID_GENERATE_V4(),
    PROJECT_NAME                    VARCHAR NOT NULL,
    ORGANISATION_ID                 UUID REFERENCES ORGANISATION (ORGANISATION_ID) NOT NULL
);
CREATE INDEX ON PROJECT (PROJECT_NAME);
CREATE INDEX ON PROJECT (ORGANISATION_ID);

For some reason running the following query:

explain analyze 
select project.project_id, project.project_name 
from project 
where project.organisation_id = 'cc06c5ab-6ada-42d2-92d5-bb551515b452';

would not hit the organisation_id index. The plan would produce a Seq Scan across the entire project table. This is obv slow as this is quite a large table.

However the following query:

explain analyze 
select project.project_id, project.project_name 
from project 
where project.organisation_id in (values('cc06c5ab-6ada-42d2-92d5-bb551515b452'::uuid));

hits the index via a Nested Loop wrapping a Bitmap Heap Scan wrapping a Bitmap Index Scan.

This index has lived in the production DB for years that first started off on Postgres 9 but has been upgraded a couple of times and it's now at Postgres 11.

After digging around to see why the first query was not hitting the organisation_id index. I decided to delete and recreate the organisation_id index in the project table.

Now all of a sudden the query:

explain analyze 
select project.project_id, project.project_name 
from project 
where project.organisation_id = 'cc06c5ab-6ada-42d2-92d5-bb551515b452';

hits the index via a Bitmap Heap Scan wrapping a Bitmap Index Scan.

Can anyone explain what is happening with Postgres? I find this behaviour confusing.

The 'old' index and the new index are both of type btree.

There is the possibility that there are so few organizations that Postgres doesn't think using the index is necessary. — Gordon Linoff
– Gordon Linoff, Commented Jun 17, 2021 at 10:48
Please edit your question and add the complete execution plans as formatted text — user330315
– user330315, Commented Jun 17, 2021 at 10:49
Unrelated, but: in (values('cc06c5ab-6ada-42d2-92d5-bb551515b452'::uuid)) can be simplified to in ('cc06c5ab-6ada-42d2-92d5-bb551515b452'::uuid) — user330315
– user330315, Commented Jun 17, 2021 at 11:17
No @AlexYu, the issue isn't reproducible after the recreation of the index. — steviemo
– steviemo, Commented Jun 18, 2021 at 12:13

Jonathan Jacobson · Accepted Answer · 2021-06-17 11:21:32Z

1

It will be impossible to tell, unless you saved a file-/block-level backup of your DB, but perhaps your index was bloated before the rebuild.

In order to understand what's happening, you should always inspect the index and column statistics. Might be too late.

The values(...) workaround must work because the planner doesn't read what's in it and assumes a general value.

answered Jun 17, 2021 at 11:21

Jonathan Jacobson

1,52811 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

steviemo Over a year ago

Yes my initial thinking was that somehow the index got bloated maybe due to the age of the index but I was thinking more people would have ran into this issue. Can you be more specific with what you mean when you say inspect the index statistics? Is there a command to execute and what would you be looking for?

Jonathan Jacobson Over a year ago

@steviemo The pgstattuple extension will provide you with insights about the index

Collectives™ on Stack Overflow

Postgres not hitting index when expected

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related