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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-sql by date

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