From: | Stephan Vollmer <svollmer(at)gmx(dot)de> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: ILIKE '%term%' and Performance |
Date: | 2005-12-05 22:37:44 |
Message-ID: | 4394C138.3070601@gmx.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi!
CSN wrote:
> I'm thinking of enabling searches that use queries like "select *
> from items where title ilike '%term%'". The items table has tens
> of thousands of rows. Is it worth worrying about the performance
> of such a query (since, if I'm not mistaken, it will never use
> indices). If it is, what's the best option - use tsearch? How
> does tsearch (or whatever else) compare performance-wise to not
> using it, or to typical index-based queries for that matter?
This is my first post to this list and I'm quite a PostgreSQL
newbie. But I had the same question as you a few days ago so maybe
my answer is helpful to you.
In my experience, there was a huge difference between using tsearch2
and normal ILIKE. This is the query time on a table with about
236000 publication records when searching for a title:
1. Without tsearch2:
SELECT * FROM publications WHERE title ILIKE '%quicksort%';
Total runtime: 1673.439 ms
2. With tsearch2:
SELECT * FROM publications
WHERE idx_fti @@ to_tsquery('default', 'quicksort');
Total runtime: 11.707 ms
So for my setup, tsearch2 was (as I expected) much faster, but of
course results will vary between different setups. But maybe it is
helpful to you.
Greetings,
- Stephan
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2005-12-05 22:53:09 | Re: feature: dynamic DB cache resizing |
Previous Message | Ed L. | 2005-12-05 22:30:19 | Re: feature: dynamic DB cache resizing |