index behavior question - multicolumn not consulted ?

From: Jonathan Vanasco <postgres(at)2xlp(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: index behavior question - multicolumn not consulted ?
Date: 2014-10-07 23:44:30
Message-ID: 708EF8C8-7ACD-44C6-B948-AF19CAE6B12A@2xlp.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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 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 ?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2014-10-07 23:51:31 Re: index behavior question - multicolumn not consulted ?
Previous Message Sergey Konoplev 2014-10-07 19:28:04 Re: Converting char to varchar automatically