On Sat, Dec 8, 2012 at 5:54 AM, Condor <condor@stz-bg.com> wrote:
> I am interested to know where is my mistake or something wrong
> with server which I doubt. Here is my current query with explain:
> (I change names to XXX YYY ZZZ because original names is written on CP1251
> and most ppl in list can't read them)
>
> db=# explain analyze SELECT *,COALESCE(firstname,'') || ' ' ||
> COALESCE(middlename,'') || ' ' || COALESCE(lastname, '') AS name FROM
> clients_tbl WHERE firstname = 'XXX' AND middlename || lastname LIKE
> '%YYY%ZZZ%';
What is the meaning/purpose of the "middlename || lastname LIKE '%YYY%ZZZ%'" ?
At least in my culture, that doesn't seem like a sensible thing to do.
Is it trying to compensate for some known dirtiness in the data that
has not yet been cleaned up?
In any event, in order to benefit from an index on that query, you
would need to create an index on the concatenated columns, not on the
individual columns.
create index on clients_tbl ((middlename||lastname) text_pattern_ops);
But that still won't work because your patterns starts with a wild
card, and that type of pattern cannot benefit from btree indexes.
...
>
> I dropped both indexes and create new one:
>
> 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.)
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.
Cheers,
Jeff