One kind of significant downside of partial indexes in PostgreSQL is that they don't work with parameters. Like, if you have a partial index on username_id, this will work:
select * from users where username_id = 5;
but this won't use the index:
select * from users where username_id = (select 5);
Basically, the query planner won't evaluate the parameter before it decides what strategy to use.
select * from users where username_id = 5;
but this won't use the index:
select * from users where username_id = (select 5);
Basically, the query planner won't evaluate the parameter before it decides what strategy to use.