From: | Jonathan Vanasco <postgres(at)2xlp(dot)com> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | function indexes, index only scan and sorting |
Date: | 2014-12-12 21:40:23 |
Message-ID: | 364A5B38-09E1-49D1-9CFB-DB5751A08789@2xlp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Can someone confirm a suspicion for me ?
I have a moderately sized table (20+ columns, 3MM rows) that tracks "tags".
I have a lower(column) function index that is used simplify case-insensitive lookups.
CREATE INDEX idx_tag_name_lower ON tag(lower(name));
I have a few complex queries that need to join back to this table (via the `id` primary key) and sort on `lower(name)`.
I'm not selecting `lower(name)`, just using it for an order-by.
The only way I seem to be able to avoid a Sequential Scan and run an index-only scan is with another index -- this one specifically (and I've run queries against 8 index permutations):
CREATE INDEX idx_tag_joins ON tag(id, name_display);
Am I correct in observing that the value of a function index can't be used for sorting ?
From | Date | Subject | |
---|---|---|---|
Next Message | Jonathan Vanasco | 2014-12-12 21:46:13 | Re: Removing duplicate records from a bulk upload (rationale behind selecting a method) |
Previous Message | Daniel Begin | 2014-12-12 21:22:05 | Re: Removing duplicate records from a bulk upload (rationale behind selecting a method) |