Re: NOT LIKE much faster than LIKE?

От: Simon Riggs
Тема: Re: NOT LIKE much faster than LIKE?
Дата: ,
Msg-id: 1136970465.21025.527.camel@localhost.localdomain
(см: обсуждение, исходный текст)
Ответ на: Re: NOT LIKE much faster than LIKE?  (Tom Lane)
Ответы: Re: NOT LIKE much faster than LIKE?  (Andrea Arcangeli)
Список: pgsql-performance

Скрыть дерево обсуждения

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, )

On Tue, 2006-01-10 at 22:40 -0500, Tom Lane wrote:
> Simon Riggs <> writes:
> > I meant use the same sampling approach as I was proposing for ANALYZE,
> > but do this at plan time for the query. That way we can apply the
> > function directly to the sampled rows and estimate selectivity.
>
> I think this is so unlikely to be a win as to not even be worth spending
> any time discussing.  The extra planning time across all queries will
> vastly outweigh the occasional improvement in plan choice for some
> queries.

Extra planning time would be bad, so clearly we wouldn't do this when we
already have relevant ANALYZE statistics.

I would suggest we do this only when all of these are true
- when accessing more than one table, so the selectivity could effect a
join result
- when we have either no ANALYZE statistics, or ANALYZE statistics are
not relevant to estimating selectivity, e.g. LIKE
- when access against the single table in question cannot find an index
to use from other RestrictInfo predicates

I imagined that this would also be controlled by a GUC, dynamic_sampling
which would be set to zero by default, and give a measure of sample size
to use. (Or just a bool enable_sampling = off (default)).

This is mentioned now because the plan under consideration in this
thread would be improved by this action. It also isn't a huge amount of
code to get it to work.

Best Regards, Simon Riggs



В списке pgsql-performance по дате сообщения:

От: Robert Creager
Дата:
Сообщение: Re: Index isn't used during a join.
От: Pallav Kalva
Дата:
Сообщение: Postgres8.0 planner chooses WRONG plan