From: Tom Lane <tgl@sss.pgh.pa.us>
To: Matteo Beccati <php@beccati.com>
Cc: Andrea Arcangeli <andrea@cpushare.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: NOT LIKE much faster than LIKE?
Date: Tue, 10 Jan 2006 12:49:55 -0500
Message-ID: <1625.1136915395@sss.pgh.pa.us> (raw)
In-Reply-To: <43C3799D.5030103@beccati.com>
References: <20060110014447.GB18474@opteron.random>
<24021.1136858688@sss.pgh.pa.us>
<20060110022303.GA20168@opteron.random>
<24372.1136861684@sss.pgh.pa.us>
<43C3799D.5030103@beccati.com>
Matteo Beccati <php@beccati.com> writes:
>> 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.
> This reminds me what I did in a patch which is currently on hold for the
> next release:
I've applied a patch to make patternsel() compute the exact result for
the MCV list, and use its former heuristics only for the portion of the
column population not included in the MCV list.
After finishing that work it occurred to me that we could go a step
further: if the MCV list accounts for a substantial fraction of the
population, we could assume that the MCV list is representative of the
whole population, and extrapolate the pattern's selectivity over the MCV
list to the whole population instead of using the existing heuristics at
all. In a situation like Andreas' example this would win big, although
you can certainly imagine cases where it would lose too.
Any thoughts about this? What would be a reasonable threshold for
"substantial fraction"? It would probably make sense to have different
thresholds depending on whether the pattern is left-anchored or not,
since the range heuristic only works for left-anchored patterns.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, php@beccati.com, andrea@cpushare.com
Subject: Re: NOT LIKE much faster than LIKE?
In-Reply-To: <1625.1136915395@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox