Re: NOT LIKE much faster than LIKE?

Поиск
Список
Период
Сортировка
От Andrea Arcangeli
Тема Re: NOT LIKE much faster than LIKE?
Дата
Msg-id 20060110034721.GC20168@opteron.random
обсуждение исходный текст
Ответ на Re: NOT LIKE much faster than LIKE?  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: NOT LIKE much faster than LIKE?  (Greg Stark <gsstark@mit.edu>)
Список pgsql-performance
On Mon, Jan 09, 2006 at 09:54:44PM -0500, Tom Lane wrote:
> Extrapolating from the observation that the heuristics don't work well
> on your data to the conclusion that they don't work for anybody is not
> good logic.  Replacing that code with a flat 50% is not going to happen
> (or if it does, I'll be sure to send the mob of unhappy users waving
> torches and pitchforks to your door not mine ;-)).

I'm not convinced but of course I cannot exclude that some people may be
depending on this very heuristic. But I consider this being
bug-compatible, I've an hard time to be convinced that such heuristic
isn't going to bite other people like it did with me.

> I did just think of something we could improve though.  The pattern
> selectivity code doesn't make any use of the statistics about "most
> common values".  For a constant pattern, we could actually apply the
> pattern test with each common value and derive answers that are exact
> for the portion of the population represented by the most-common-values
> list.  If the MCV list covers a large fraction of the population then
> this would be a big leg up in accuracy.  Dunno if that applies to your
> particular case or not, but it seems worth doing ...

Fixing this with proper stats would be great indeed. What would be the
most common value for the kernel_version? You can see samples of the
kernel_version here http://klive.cpushare.com/2.6.15/ .  That's the
string that is being searched against both PREEMPT and SMP.

BTW, I also run a LIKE '%% SMP %%' a NOT LIKE '%% SMP %%' but that runs
fine, probably because as you said in the first email PREEMPT is long
but SMP is short.

Thanks!

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

Предыдущее
От: Stephan Szabo
Дата:
Сообщение: Re: NOT LIKE much faster than LIKE?
Следующее
От: Robert Creager
Дата:
Сообщение: Index isn't used during a join.