Re: String searching
От
Jonathan Vanasco
Тема
Re: String searching
Дата
Msg-id
16B6D4DC-8B25-4B80-8C25-B759308BD04C@2xlp.com
Ответ на
Re: String searching (Albe Laurenz)
Список
Дерево обсуждения
String searching Robert DiFalco <robert.difalco@gmail.com>
Re: String searching Jonathan Vanasco <postgres@2xlp.com>
Re: String searching David G Johnston <david.g.johnston@gmail.com>
Re: String searching Andy Colson <andy@squeakycode.net>
Re: String searching Albe Laurenz <laurenz.albe@wien.gv.at>
Re: String searching Jonathan Vanasco <postgres@2xlp.com>
Re: String searching Robert DiFalco <robert.difalco@gmail.com>
Re: String searching Kevin Grittner <kgrittn@ymail.com>
Re: String searching Jonathan Vanasco <postgres@2xlp.com>
Re: String searching Vick Khera <vivek@khera.org>
On Nov 18, 2014, at 7:38 AM, Albe Laurenz wrote:
That index wouldn't help with the query at all.
If you really need a full substring search (i.e., you want to find
"howardjohnson"), the only thing that could help are trigram indexes.
I stand corrected.
I ran a sample query on my test database of 100k names
using a function index `lower(name)`
this runs an index scan in .2ms
... where lower(name) = lower('bob');
but this runs a sequential scan in 90ms:
... where lower(name) like lower('%bob%');
I didn't know that 'like' doesn't run on indexes!
using a trigaram index,
this runs a bitmap index on the trigram, then a bitmap heap on the table. 13ms.
...where name ilike '%bob%';
В списке pgsql-general по дате отправления
