On Sun, Dec 9, 2012 at 10:59 PM, Condor <condor@xxxxxxxxxx> wrote: > On 2012-12-10 00:31, Jeff Janes wrote: >> >> On Sat, Dec 8, 2012 at 5:54 AM, Condor <condor@xxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
