Skip site navigation (1) Skip section navigation (2)

Re: Very slow search using basic pattern matching

From: Dan Field <dof(at)llgc(dot)org(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Very slow search using basic pattern matching
Date: 2004-05-19 11:33:22
Message-ID: 55B02154-A988-11D8-9565-000A958E367A@llgc.org.uk (view raw or flat)
Thread:
Lists: pgsql-sql
On 18 May 2004, at 17:12, Bruno Wolff III wrote:

> On Tue, May 18, 2004 at 16:47:11 +0100,
>   Dan Field <dof(at)llgc(dot)org(dot)uk> wrote:
>> FROM
>> 	da_records
>> WHERE
>> 	da_records.DESCRIPTION_CY ~* '.*Aberystwyth*.'
>>  OR
>> 	da_records.TITLE_CY ~* '.*Aberystwyth*.'
>> limit 100
>>
>> Is there a better way of matching the string? (Must be case 
>> insensitive)
>
> This is going to require a sequential scan and if you aren't going to
> hit 100 entries near the beginning of the table, you will be 
> effectively
> doing a full scan of the table.
>
> You might try looking at the tsearch2 contrib entry (a package for 
> full text
> searching) as that will likely be able to help you out.

OK It looks like the full text search is the way forward here, but I 
can only find the tsearch.sql file in contrib. I'm using RedHat EL 3.0 
Advanced Server with PG version 7.3-RH.

Is there any documentation available on how to go about creating these 
indexes on my text fields?

I'm at a bit of a loss here, Google is failing me sadly

-- 
Dan Field <dof(at)llgc(dot)org(dot)uk> - Support Programmer: Cymru ar y we
cy_GB: http://www.cymruarywe.org
en_GB: http://www.walesontheweb.org


In response to

Responses

pgsql-sql by date

Next:From: Raphael A. BauerDate: 2004-05-19 11:51:57
Subject: Re: Very slow search using basic pattern matching
Previous:From: Christoph HallerDate: 2004-05-19 10:55:22
Subject: Re: a wierd query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group