From: Yantao Shi <y.shi@larc.nasa.gov>
To: pgsql-performance@postgresql.org
Subject: wildcard search performance with "like"
Date: Tue, 17 Jan 2006 15:00:37 -0500
Message-ID: <43CD4CE5.3020506@larcb.ecs.nasa.gov> (raw)
Hi,
I have a postges 8.1.1 table with over 29 million rows in it. The colunm
(file_name) that I need to search on has entries like the following:
MOD04_L2.A2005311.1400.004.2005312013848.hdf
MYD04_L2.A2005311.0700.004.2005312013437.hdf
I have an index on this column. But an index search is performance only
when I give the full file_name for search:
testdbspc=# explain select file_name from catalog where file_name =
'MOD04_L2.A2005311.1400.004.2005312013848.hdf';
QUERY PLAN
Index Scan using catalog_pk_idx on catalog (cost=0.00..6.01 rows=1
width=404)
Index Cond: (file_name =
'MOD04_L2.A2005311.1400.004.2005312013848.hdf'::bpchar)
(2 rows)
What I really need to do most of the time is a multi-wildcard search on
this column, which is now doing a whole table scan without using the
index at all:
testdbspc=# explain select file_name from catalog where file_name like
'MOD04_L2.A2005311.%.004.2005312013%.hdf';
QUERY PLAN
Seq Scan on catalog (cost=0.00..429.00 rows=1 width=404)
Filter: (file_name ~~ 'MOD04_L2.A2005311.%.004.2005312013%.hdf'::text)
(2 rows)
Obviously, the performance of the table scan on such a large table is
not acceptable.
I tried full-text indexing and searching. It did NOT work on this column
because all the letters and numbers are linked together with "." and
considered one big single word by to_tsvector.
Any solutions for this column to use an index search with multiple wild
cards?
Thanks a lot,
Yantao Shi
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: y.shi@larc.nasa.gov
Subject: Re: wildcard search performance with "like"
In-Reply-To: <43CD4CE5.3020506@larcb.ecs.nasa.gov>
* 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