pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwildcard search performance with "like"
3+ messages / 3 participants
[nested] [flat]
* wildcard search performance with "like"
@ 2006-01-17 20:00 Yantao Shi <y.shi@larc.nasa.gov>
2006-01-17 21:01 ` Re: wildcard search performance with "like" Tom Lane <tgl@sss.pgh.pa.us>
2006-01-18 15:06 ` Re: wildcard search performance with "like" Michael Riess <mlriess@gmx.de>
0 siblings, 2 replies; 3+ messages in thread
From: Yantao Shi @ 2006-01-17 20:00 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: wildcard search performance with "like"
2006-01-17 20:00 wildcard search performance with "like" Yantao Shi <y.shi@larc.nasa.gov>
@ 2006-01-17 21:01 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-01-17 21:01 UTC (permalink / raw)
To: Yantao Shi <y.shi@larc.nasa.gov>; +Cc: pgsql-performance
Yantao Shi <y.shi@larc.nasa.gov> writes:
> 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)
I'm betting you are using a non-C locale. You need either to run the
database in C locale, or to create a special index type that is
compatible with LIKE searches. See
http://www.postgresql.org/docs/8.1/static/indexes-opclass.html
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: wildcard search performance with "like"
2006-01-17 20:00 wildcard search performance with "like" Yantao Shi <y.shi@larc.nasa.gov>
@ 2006-01-18 15:06 ` Michael Riess <mlriess@gmx.de>
1 sibling, 0 replies; 3+ messages in thread
From: Michael Riess @ 2006-01-18 15:06 UTC (permalink / raw)
To: pgsql-performance
As far as I know the index is only used when you do a prefix search, for
example
col like 'xyz%'
I think that if you are looking for expressions such as 'A%B', you could
rephrase them like this:
col like 'A%' AND col like 'A%B'
So the database could use the index to narrow down the result and then
do a sequential search for the second condition.
Mike
Yantao Shi schrieb:
> 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-01-18 15:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-17 20:00 wildcard search performance with "like" Yantao Shi <y.shi@larc.nasa.gov>
2006-01-17 21:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-18 15:06 ` Michael Riess <mlriess@gmx.de>
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