On 10/7/2014 4:44 PM, Jonathan Vanasco wrote:
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 seeing a 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 ?
the index (resource_type_id, lower(archive_pathname)) should certainly get used on queries like...
select <stuff> from table where resource_type_id = $1 and lower(archive_pathname) = $2; the 2nd = could be > or < too.
It also should get used on queries with resource_type_id = $1, unless thats not very selective (millions of records with just a few resource_type_id values, for instance).
-- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
