Re: NOT LIKE much faster than LIKE?

From: Tom Lane
Subject: Re: NOT LIKE much faster than LIKE?
Date: ,
Msg-id: 24372.1136861684@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: NOT LIKE much faster than LIKE?  (Andrea Arcangeli)
Responses: Re: NOT LIKE much faster than LIKE?  (Andrea Arcangeli)
Re: NOT LIKE much faster than LIKE?  (Matteo Beccati)
List: pgsql-performance

Tree view

NOT LIKE much faster than LIKE?  (Andrea Arcangeli, )
 Re: NOT LIKE much faster than LIKE?  (Tom Lane, )
  Re: NOT LIKE much faster than LIKE?  (Andrea Arcangeli, )
   Re: NOT LIKE much faster than LIKE?  (Christopher Kings-Lynne, )
    Re: NOT LIKE much faster than LIKE?  (Andrea Arcangeli, )
   Re: NOT LIKE much faster than LIKE?  (Tom Lane, )
    Re: NOT LIKE much faster than LIKE?  (Andrea Arcangeli, )
     Re: NOT LIKE much faster than LIKE?  (Greg Stark, )
    Re: NOT LIKE much faster than LIKE?  (Matteo Beccati, )
     Re: NOT LIKE much faster than LIKE?  (Tom Lane, )
      Re: NOT LIKE much faster than LIKE?  (Simon Riggs, )
       Re: NOT LIKE much faster than LIKE?  (Tom Lane, )
        Re: NOT LIKE much faster than LIKE?  (Simon Riggs, )
         Re: NOT LIKE much faster than LIKE?  (Tom Lane, )
          Re: NOT LIKE much faster than LIKE?  (Simon Riggs, )
           Re: NOT LIKE much faster than LIKE?  (Andrea Arcangeli, )
        Re: NOT LIKE much faster than LIKE?  (Simon Riggs, )
   Re: NOT LIKE much faster than LIKE?  (Stephan Szabo, )
 Re: NOT LIKE much faster than LIKE?  (Andrea Arcangeli, )
  Re: NOT LIKE much faster than LIKE?  (Tom Lane, )
   Re: NOT LIKE much faster than LIKE?  (Andrea Arcangeli, )
 Re: NOT LIKE much faster than LIKE?  ("Jim C. Nasby", )
  Re: NOT LIKE much faster than LIKE?  (Andrea Arcangeli, )
   Re: NOT LIKE much faster than LIKE?  (Andrea Arcangeli, )
   Re: NOT LIKE much faster than LIKE?  ("Jim C. Nasby", )
    Re: NOT LIKE much faster than LIKE?  (Tom Lane, )

Andrea Arcangeli <> writes:
> If you don't know the data, I think it's a bug that LIKE is assumed to
> have a selectivity above 50%.

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

            regards, tom lane


pgsql-performance by date:

From: Robert Creager
Date:
Subject: Index isn't used during a join.
From: Michael Fuhr
Date:
Subject: Re: Index isn't used during a join.