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?

1 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.

  • 2
    Great work! It was just the analyze issue - after running analyze, it works perfectly.
    – Brent Ozar
    Commented Feb 19 at 16:47
  • Nice, thanks, I realised that after posting and was trying to get an edit in before you noticed :) Commented Feb 19 at 16:50
  • 5
    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. Commented Feb 20 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.