From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrea Arcangeli <andrea@cpushare.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: NOT LIKE much faster than LIKE?
Date: Mon, 09 Jan 2006 21:04:48 -0500
Message-ID: <24021.1136858688@sss.pgh.pa.us> (raw)
In-Reply-To: <20060110014447.GB18474@opteron.random>
References: <20060110014447.GB18474@opteron.random>
Andrea Arcangeli <andrea@cpushare.com> writes:
> It just makes no sense to me that the planner takes a difference
> decision based on a "not".
Why in the world would you think that? In general a NOT will change the
selectivity of the WHERE condition tremendously. If the planner weren't
sensitive to that, *that* would be a bug. The only case where it's
irrelevant is if the selectivity of the base condition is exactly 50%,
which is not a very reasonable default guess for LIKE.
It sounds to me that the problem is misestimation of the selectivity
of the LIKE pattern --- the planner is going to think that
LIKE '%% PREEMPT %%' is fairly selective because of the rather long
match text, when in reality it's probably not so selective on your
data. But we don't keep any statistics that would allow the actual
number of matching rows to be estimated well. You might want to think
about changing your data representation so that the pattern-match can be
replaced by a boolean column, or some such, so that the existing
statistics code can make a more reasonable estimate how many rows are
selected.
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, andrea@cpushare.com
Subject: Re: NOT LIKE much faster than LIKE?
In-Reply-To: <24021.1136858688@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