LIKE without wildcard different from =

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема LIKE without wildcard different from =
Дата
Msg-id 4C59503402000025000341C0@gw.wicourts.gov
обсуждение исходный текст
Ответы Re: LIKE without wildcard different from =  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
We have been using the C locale for everything at our site, but
there is occasionally talk of supporting characters outside the
ASCII7 set. In playing around with indexing, to see what the impact
of that would be, I stumbled across something which was mildly
surprising.

In the C locale, if you want to search for an exact value which
doesn't contain wildcard characters, it doesn't matter whether you
use the 'LIKE' operator or the '=' operator.  With LATIN1 encoding,
it made three orders of magnitude difference, both in the estimated
cost and the actual run time.  I'm not entirely clear on whether it
would be *incorrect* for PostgreSQL to automatically turn the second
query below into the first, or just too expensive an optimization to
check for compared to how often it might help.

"SccaParty_SearchName" btree ("searchName" varchar_pattern_ops)

explain analyze select "searchName" from "SccaParty"
  where "searchName" like 'SMITH,JOHNBRACEYJR';

 Index Scan using "SccaParty_SearchName" on "SccaParty"
     (cost=0.00..2.94 rows=22 width=18)
     (actual time=0.046..0.051 rows=2 loops=1)
   Index Cond: (("searchName")::text ~=~ 'SMITH,JOHNBRACEYJR'::text)
   Filter: (("searchName")::text ~~ 'SMITH,JOHNBRACEYJR'::text)
 Total runtime: 0.083 ms

explain analyze select "searchName" from "SccaParty"
  where "searchName" = 'SMITH,JOHNBRACEYJR';

 Seq Scan on "SccaParty"
     (cost=0.00..3014.49 rows=22 width=18)
     (actual time=2.395..54.228 rows=2 loops=1)
   Filter: (("searchName")::text = 'SMITH,JOHNBRACEYJR'::text)
 Total runtime: 54.274 ms

I don't have a problem, and am not suggesting any action; just
trying to understand this.

-Kevin

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Kevin Grittner"
Дата:
Сообщение: Re: Questions on query planner, join types, and work_mem
Следующее
От: Tom Lane
Дата:
Сообщение: Re: LIKE without wildcard different from =