Date: Tue, 10 Jan 2006 03:45:34 +0100
From: Andrea Arcangeli <andrea@cpushare.com>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: NOT LIKE much faster than LIKE?
Message-ID: <20060110024534.GB20168@opteron.random>
References: <20060110014447.GB18474@opteron.random>
	<24021.1136858688@sss.pgh.pa.us>
	<20060110022303.GA20168@opteron.random>
	<43C31BF1.704@familyhealth.com.au>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <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".