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

Re: Very slow search using basic pattern matching

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Dan Field <dof(at)llgc(dot)org(dot)uk>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Very slow search using basic pattern matching
Date: 2004-05-18 16:12:56
Message-ID: 20040518161256.GA911@wolff.to (view raw or flat)
Thread:
Lists: pgsql-sql
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.

In response to

Responses

pgsql-sql by date

Next:From: hookDate: 2004-05-18 16:19:16
Subject: I'm baffled
Previous:From: Dan FieldDate: 2004-05-18 15:47:11
Subject: Very slow search using basic pattern matching

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