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