Very slow search using basic pattern matching

Поиск
Список
Период
Сортировка
От Dan Field
Тема Very slow search using basic pattern matching
Дата
Msg-id A02B0582-A8E2-11D8-9565-000A958E367A@llgc.org.uk
обсуждение исходный текст
Ответы Re: Very slow search using basic pattern matching  (Bruno Wolff III <bruno@wolff.to>)
Re: Very slow search using basic pattern matching  (Karsten Hilbert <Karsten.Hilbert@gmx.net>)
Список 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@llgc.org.uk> - Support Programmer: Cymru ar y we
cy_GB: http://www.cymruarywe.org
en_GB: http://www.walesontheweb.org

В списке pgsql-sql по дате отправления:

Предыдущее
От: "kumar"
Дата:
Сообщение: Opening and Fetching from a refcursor inside a function
Следующее
От: Bruno Wolff III
Дата:
Сообщение: Re: Very slow search using basic pattern matching