From: Richard Huxton <dev@archonet.com>
To: mark@mark.mielke.cc
Cc: Mark Lewis <mark.lewis@mir3.com>
Cc: James Mansion <james@mansionfamily.plus.com>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Alexander Staubo <alex@purefiction.net>
Cc: Andy <frum@ar-sd.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: LIKE search and performance
Date: Fri, 25 May 2007 09:13:25 +0100
Message-ID: <46569AA5.10205@archonet.com> (raw)
In-Reply-To: <20070524215450.GA3858@mark.mielke.cc>
References: <009501c79d4c$445428f0$0b00a8c0@mpsro.dom>
<88daf38c0705230905u1a981102k691b47bd36011a80@mail.gmail.com>
<4655DE75.4080506@mansionfamily.plus.com>
<4655E64B.5050503@hagander.net>
<4655FB8A.1040707@mansionfamily.plus.com>
<1180040560.31471.262.camel@archimedes>
<20070524215450.GA3858@mark.mielke.cc>
mark@mark.mielke.cc wrote:
>> And since it's basically impossible to know the selectivity of this kind
>> of where condition, I doubt the planner would ever realistically want to
>> choose that plan anyway because of its poor worst-case behavior.
>
> What is a real life example where an intelligent and researched
> database application would issue a like or ilike query as their
> primary condition in a situation where they expected very high
> selectivity?
>
> Avoiding a poor worst-case behaviour for a worst-case behaviour that
> won't happen doesn't seem practical.
But if you are also filtering on e.g. date, and that has an index with
good selectivity, you're never going to use the text index anyway are
you? If you've only got a dozen rows to check against, might as well
just read them in.
The only time it's worth considering the behaviour at all is *if* the
worst-case is possible.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, mark@mark.mielke.cc, mark.lewis@mir3.com, james@mansionfamily.plus.com, magnus@hagander.net, alex@purefiction.net, frum@ar-sd.net
Subject: Re: LIKE search and performance
In-Reply-To: <46569AA5.10205@archonet.com>
* 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