From: Andrea Arcangeli <andrea@cpushare.com>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: NOT LIKE much faster than LIKE?
Date: Tue, 10 Jan 2006 03:45:34 +0100
Message-ID: <20060110024534.GB20168@opteron.random> (raw)
In-Reply-To: <43C31BF1.704@familyhealth.com.au>
References: <20060110014447.GB18474@opteron.random>
<24021.1136858688@sss.pgh.pa.us>
<20060110022303.GA20168@opteron.random>
<43C31BF1.704@familyhealth.com.au>
On Tue, Jan 10, 2006 at 10:29:05AM +0800, Christopher Kings-Lynne wrote:
> > UNLIKELY string LIKE '%% PREEMPT %%'
> >
> >or:
> >
> > LIKELY string NOT LIKE '%% PREEMPT %%'
>
> You should be using contrib/tsearch2 for an un-anchored text search perhaps?
If I wanted to get the fastest speed possible, then I think parsing it
with python and storing true/false in a boolean like suggested before
would be better and simpler as well for this specific case.
However I don't need big performance, I need just decent performance, and it
annoys me that there heurisics where the LIKE query assumes little data
will be selected. There's no way to know that until proper stats are
recorded on the results of the query. The default should be good enough
to use IMHO, and there's no way to know if NOT LIKE or LIKE will return
more data, 50% should be assumed for both if no runtime information is
available IMHO.
IIRC gcc in a code like if (something) {a} else {b} assumes that a is
more likely to be executed then b, but that's because it's forced to
choose something. Often one is forced to choose what is more likely
between two events, but I don't think the above falls in this case. I
guess the heuristic really wanted to speed up the runtime of LIKE, when
it actually made it a _lot_ worse. No heuristic is better than an
heuristic that falls apart in corner cases like the above "LIKE".
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: andrea@cpushare.com, chriskl@familyhealth.com.au, tgl@sss.pgh.pa.us
Subject: Re: NOT LIKE much faster than LIKE?
In-Reply-To: <20060110024534.GB20168@opteron.random>
* 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