Re: -HEAD planner issue wrt hash_joins on dbt3 ?

Hi,

Tom Lane wrote:
> Hmm ... pattern_sel already applies the operator directly to the
> most_common_vals, but in this situation those aren't common enough
> to help much. With such an extensive histogram it is awfully tempting
> to assume that the histogram members are a representative sample, and
> take the selectivity as being the fraction of histogram entries that
> match the pattern. Maybe drop the first and last histogram entries
> on the grounds they're probably outliers. Thoughts? What would be a
> reasonable minimum histogram size to enable using this approach instead
> of the guess-on-the-basis-of-the-pattern code?

That's what I was suggesting here respectively for ltree operators and like:

http://archives.postgresql.org/pgsql-patches/2006-05/msg00178.php
http://archives.postgresql.org/pgsql-performance/2006-01/msg00083.php

My original ltree patch was stripped of the histogram matching code and
I will need to re-patch 8.2 when deploying it to get decent performance
with a couple of queries, but it would be very nice to avoid it ;)

I cannot see anything bad by using something like that:

if (histogram is large/representative enough)
{
recalculate_selectivity_matching_histogram_values()

if (new_selectivity > old_selectivity)
return new_selectivity
else
return old_selectivity
}

Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com