Date: Fri, 25 May 2007 00:09:15 +0200
To: "Mark Lewis" <mark.lewis@mir3.com>,
	"James Mansion" <james@mansionfamily.plus.com>
Subject: Re: LIKE search and performance
From: PFC <lists@peufeu.com>
Cc: "Magnus Hagander" <magnus@hagander.net>,
	"Alexander Staubo" <alex@purefiction.net>, Andy <frum@ar-sd.net>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
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>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.tsuqhpzycigqcu@apollo13>
In-Reply-To: <1180040560.31471.262.camel@archimedes>
User-Agent: Opera Mail/9.10 (Linux)


> PG could scan the index looking for matches first and only load the
> actual rows if it found a match, but that could only be a possible win=

> if there were very few matches, because the difference in cost between=
 a
> full index scan and a sequential scan would need to be greater than th=
e
> cost of randomly fetching all of the matching data rows from the table=

> to look up the visibility information.

	If you need to do that kind of thing, ie. seq scanning a table checking=
  =

only one column among a large table of many columns, then don't use an  =

index. An index, being a btree, needs to be traversed in order (or else,=
 a  =

lot of locking problems come up) which means some random accesses.

	So, you could make a table, with 2 columns, updated via triggers : your=
  =

text field, and the primary key of your main table. Scanning that would =
be  =

faster.

	Still, a better solution for searching in text is :

	- tsearch2 if you need whole words
	- trigrams for any substring match
	- xapian for full text search with wildcards (ie. John* =3D Johnny)

	Speed-wise those three will beat any seq scan on a large table by a hug=
e  =

margin.