From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: index behavior question - multicolumn not consulted ? |
Date: | 2014-10-07 23:51:31 |
Message-ID: | 54347C83.3010205@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2014-10-08 00:29:22 | Re: table versioning approach (not auditing) |
Previous Message | Jonathan Vanasco | 2014-10-07 23:44:30 | index behavior question - multicolumn not consulted ? |