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:

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.