I have a table with over 1MM records and 15 columns.
I had created a "unique index" on a mix of two columns to enforce a constraint : (resource_type_id,
lower(archive_pathname))
i've noticed that searches never use this. no matter what I query, even if it's only the columns in the index. I'm
seeinga 550ms sequential scan on everything.
If I create an index only on the text field: lower(archive_pathname) , all the queries use that and complete in 1.4ms
does anyone know why this happens ?