pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
What index for 'like (%keyword%)' ???
3+ messages / 3 participants
[nested] [flat]

* What index for 'like (%keyword%)' ???
@ 2004-03-31 09:51  Priem, Alexander <ap@cict.nl>
  0 siblings, 2 replies; 3+ messages in thread

From: Priem, Alexander @ 2004-03-31 09:51 UTC (permalink / raw)
  To: pgsql-performance

Hi everyone,

I am building a query which uses a clause like "Where doc_description like
'%keyword%'". I know a normal index won't be of any use here, but since the
table in question will get fairly big, I do want to use an index.

Can anyone give me some advise on what kind of index I can use here? Or
shouldn't I use one in this case?

Kind regards,
Alexander Priem.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: What index for 'like (%keyword%)' ???
@ 2004-03-31 12:07  Richard Huxton <dev@archonet.com>
  parent: Priem, Alexander <ap@cict.nl>
  1 sibling, 0 replies; 3+ messages in thread

From: Richard Huxton @ 2004-03-31 12:07 UTC (permalink / raw)
  To: Priem, Alexander <ap@cict.nl>; pgsql-performance

On Wednesday 31 March 2004 10:51, Priem, Alexander wrote:
> Hi everyone,
>
> I am building a query which uses a clause like "Where doc_description like
> '%keyword%'". I know a normal index won't be of any use here, but since the
> table in question will get fairly big, I do want to use an index.
>
> Can anyone give me some advise on what kind of index I can use here? Or
> shouldn't I use one in this case?

You probably want to look at the contrib/tsearch2 full-text indexing module.

-- 
  Richard Huxton
  Archonet Ltd



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: What index for 'like (%keyword%)' ???
@ 2004-04-01 01:19  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Priem, Alexander <ap@cict.nl>
  1 sibling, 0 replies; 3+ messages in thread

From: Christopher Kings-Lynne @ 2004-04-01 01:19 UTC (permalink / raw)
  To: Priem, Alexander <ap@cict.nl>; +Cc: pgsql-performance

> I am building a query which uses a clause like "Where doc_description like
> '%keyword%'". I know a normal index won't be of any use here, but since the
> table in question will get fairly big, I do want to use an index.
> 
> Can anyone give me some advise on what kind of index I can use here? Or
> shouldn't I use one in this case?

You have to use a proper full text indexing scheme.  Investigate 
contrib/tsearch2 module in the postgres distribution.

Chirs




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-04-01 01:19 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-31 09:51 What index for 'like (%keyword%)' ??? Priem, Alexander <ap@cict.nl>
2004-03-31 12:07 ` Richard Huxton <dev@archonet.com>
2004-04-01 01:19 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>

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