CODEWITHSUNDEEP
sqlpostgresql
steviemo
steviemo

Reputation: 104

Postgres not hitting index when expected

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.

Upvotes: 2

Views: 151

Answers (1)

Jonathan Jacobson
Jonathan Jacobson

Reputation: 1518

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.

Upvotes: 1

Related Questions