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

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:
>
>> 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.
>
>
> ...

The point is that the first server should fulfill the condition which
is equal sign and then move on to the rest condition. I can use it as a
above example or query like bellow:
SELECT *,COALESCE(firstname,'') || ' ' || COALESCE(middlename,'') || '
' || COALESCE(lastname, '') AS name FROM clients_tbl WHERE firstname =
'XXX' AND middlename || lastname LIKE
'%ZZZ%';
In this case I don't know where actually is ZZZ in the middle or in
lastname because that is the input. Also can be:

SELECT *,COALESCE(firstname,'') || ' ' || COALESCE(middlename,'') || '
' || COALESCE(lastname, '') AS name FROM clients_tbl WHERE firstname =
'XXX' AND middlename || lastname LIKE
'%Y%ZZZ%';

First part of the middle name only Y not YYY full middle name.

And it's work fine.

>>
>> 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.)
>

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. When i make index like:

create index on clients_tbl (middlename text_pattern_ops);
or
create index on clients_tbl (firstname text_pattern_ops);

there is not different result ... 35 ms but I expect to dropped from 35
to 20 or 10 ms :)

> 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 ?
To make two indexes one with "text_pattern_ops" other without it ?

> Cheers,
>
> Jeff

Regards,
H.S.