Re: Query and index ... unexpected result need advice.

On Sun, Dec 9, 2012 at 10:59 PM, Condor <condor(at)stz-bg(dot)com> wrote:
> On 2012-12-10 00:31, Jeff Janes wrote:
>>
>> On Sat, Dec 8, 2012 at 5:54 AM, Condor <condor(at)stz-bg(dot)com> wrote:
>>

>>> create index clients_tbl_firstname_idx on clients_tbl using btree
>>> (firstname
>>> COLLATE "bg_BG" text_pattern_ops);
>>
>>
>> I don't understand why that is legal. I would think that
>> text_pattern_ops implies something that contradicts COLLATE "bg_BG".
>> In any event, the inclusion of both of those seems to prevent the
>> index from being used for equality, while the inclusion of just one or
>> the other property does not. (That is why the query got slower.)
>>
>
> I was thinking when I add COLLATE "bg_BG" text_pattern_ops it's will help to
> indexer to understand that data there is in specific encoding and
> will speed up like clause.

The text_pattern_ops tells it to use an collation which supports
(some) like clauses, while COLLATE "bg_BG" tells it to use that named
collation.

I think that text_pattern_ops is almost identical to COLLATE "C". But
COLLATE was not possible until 9.1 while the op_class has been around
for much longer.

>> Since firstname is used as equality in your example, there is no
>> reason to change this index to "text_pattern_ops" in order to support
>> your example.
>>
>
> Understand that, but if I need to do like in firstname what is the solution?

By experimentation, if you just do text_pattern_ops, then that
supports both equality and LIKE. It will not support <, >, between.

> To make two indexes one with "text_pattern_ops" other without it ?

Yes. This is what the documentation recommends. It is sometimes not
necessary, but I know of no way to determine when it is needed, other
than experimentation with the exact encoding/collation you have and
the types of queries you want to support.

Cheers,

Jeff