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

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: Very slow search using basic pattern matching
Date: 2004-05-18 15:47:11
Message-ID: A02B0582-A8E2-11D8-9565-000A958E367A@llgc.org.uk (view raw or flat)
Thread:
Lists: pgsql-sql
I'm trying to use the following statement as part of a search facility 
on a site I develop. However the search is taking in excess of 10 
seconds some times to return results. I'ts a dual opteron server with a 
couple gigs of RAM so should be more than enough to deal with this 
database.


SELECT
	da_records.RECORD_ID,
	da_records.TITLE_EN AS TITLE,
	da_records.AUTHOR_EN AS AUTHOR,
	da_records.DESCRIPTION_CY AS DESCRIPTION,
	da_records.PUBLISHER_CY AS PUBLISHER,
	da_records.URL_CY AS URL,
	da_records.RECORD_ID,
	da_records.KEYWORD_LIST_ID,
	da_records.LANGUAGE,
	da_records.CONTRIBUTOR_NAME,
	da_records.CONTRIBUTOR_EMAIL,
	da_records.CONTRIBUTOR_ORGANISATION,
	da_records.CONTRIBUTOR_CREDIT,
	da_records.DEWEY_LIST_ID,
	da_records.LISTING_PRIORITY,
	da_records.SUBMITTED_DATE,
	da_records.LAST_EDIT_DATE,
	da_records.STATUS
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)

TIA
-- 
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

Responses

pgsql-sql by date

Next:From: Bruno Wolff IIIDate: 2004-05-18 16:12:56
Subject: Re: Very slow search using basic pattern matching
Previous:From: kumarDate: 2004-05-18 12:18:47
Subject: Opening and Fetching from a refcursor inside a function

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