From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: performance of like queries
Date: Tue, 02 Oct 2007 17:55:08 -0400
Message-ID: <60abr1mbb7.fsf@dba2.int.libertyrms.com> (raw)
References: <200710021131.46010.kevin@kevinkempterllc.com>
kevin@kevinkempterllc.com (Kevin Kempter) writes:
> any suggestions for improving "LIKE '%text%'" queries?
If you know that the 'text' portion of that query won't change, then
you might create a partial index on the boolean condition.
That is,
create index index_foo_text on my_table (tfield) where (tfield like '%text%');
I somehow doubt that is the case; more likely you want to be able to
search for:
select * from my_table where tfield like '%this%';
select * from my_table where tfield like '%that%';
select * from my_table where tfield like '%the other thing%';
There are basically three choices, at that point:
1. Get more memory, and hope that you can have all the data get
cached in memory.
2. Get more better disk, so that you can scan the table faster on
disk.
3. Look into tsearch2, which provides a full text search capability.
--
(format nil "~S@~S" "cbbrowne" "linuxdatabases.info")
http://cbbrowne.com/info/x.html
"We're born with a number of powerful instincts, which are found
across all cultures. Chief amongst these are a dislike of snakes, a
fear of falling, and a hatred of popup windows" -- Vlatko Juric-Kokic
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: performance of like queries
In-Reply-To: <60abr1mbb7.fsf@dba2.int.libertyrms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox