Thread: index behavior question - multicolumn not consulted ?

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 ?

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

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