Why Isn't Postgres Using My Functional Index?

Question

I have a public copy of the Stack Overflow database here on AWS Aurora Postgres 15.5:

• Server: query.smartpostgres.com
• Username: readonly
• Password: 511e0479-4d35-49ab-98b1-c3a9d69796f4

The users table has this index:

create index users_length_displayname on users(length(displayname));

But when I run either of these queries:

select * from users where length(displayname) > 35;
select length(displayname) from users where length(displayname) > 35;

They don't use the functional index, as evidenced by their query plans:

Query plan showing table scan

So, uh, why?

Answer 1

I think this is a statistics/estimation issue.

I initially thought it might also be a datatype casting issue, since the column is character varying(40) and the index is using text but now realise Postgres is converting those for us.

I did manage to get it to use the index (in a bitmap scan) for the following different-but-still-awfully-estimated exact match query:

explain select * from users where (length(displayname)) = 35;

This proves it can use the index when the estimates are better, even if they're still way off.

If you run the following, does it use the index?

analyze users;
explain select * from users where (length(displayname)) > 35;

I couldn't verify this, as the user is read-only and therefore can't run analyze.

• Brent Ozar: Great work! It was just the analyze issue - after running analyze, it works perfectly.
• michristofides: Nice, thanks, I realised that after posting and was trying to get an edit in before you noticed :)
• Laurenz Albe: An index on an expression makes PostgreSQL collect statistics for the indexed expression, but CREATE INDEX doesn't trigger an autoanalyze run, so you have to do it yourself.