pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedoptimizing LIKE '%2345' queries
5+ messages / 4 participants
[nested] [flat]
* optimizing LIKE '%2345' queries
@ 2006-07-02 21:50 Gene <genekhart@gmail.com>
2006-07-03 07:33 ` Re: optimizing LIKE '%2345' queries Mario Weilguni <mweilguni@sime.com>
2006-07-04 11:14 ` Re: optimizing LIKE '%2345' queries Tarhon-Onu Victor <mituc@iasi.rdsnet.ro>
0 siblings, 2 replies; 5+ messages in thread
From: Gene @ 2006-07-02 21:50 UTC (permalink / raw)
To: pgsql-performance
Is there any way to create a reverse index on string columns so that
queries of the form:
where column like '%2345';
can use an index and perform as fast as searching with like '2345%'?
Is the only way to create a reverse function and create an index using
the reverse function and modify queries to use:
where reverse(column) like reverse('%2345') ?
thanks
--
Eugene Hart
Cell: 443-604-2679
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: optimizing LIKE '%2345' queries
2006-07-02 21:50 optimizing LIKE '%2345' queries Gene <genekhart@gmail.com>
@ 2006-07-03 07:33 ` Mario Weilguni <mweilguni@sime.com>
1 sibling, 0 replies; 5+ messages in thread
From: Mario Weilguni @ 2006-07-03 07:33 UTC (permalink / raw)
To: pgsql-performance; genekhart@gmail.com
Am Sonntag, 2. Juli 2006 23:50 schrieb Gene:
> Is there any way to create a reverse index on string columns so that
> queries of the form:
>
> where column like '%2345';
>
> can use an index and perform as fast as searching with like '2345%'?
>
> Is the only way to create a reverse function and create an index using
> the reverse function and modify queries to use:
>
> where reverse(column) like reverse('%2345') ?
>
> thanks
create a trigger that computes this at insert/update time, index this fix, and
rewrite the query this way:
where inverted_column like '5432%';
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: optimizing LIKE '%2345' queries
2006-07-02 21:50 optimizing LIKE '%2345' queries Gene <genekhart@gmail.com>
@ 2006-07-04 11:14 ` Tarhon-Onu Victor <mituc@iasi.rdsnet.ro>
2006-07-04 20:27 ` Re: optimizing LIKE '%2345' queries Gene <genekhart@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tarhon-Onu Victor @ 2006-07-04 11:14 UTC (permalink / raw)
To: pgsql-performance; +Cc: Gene <genekhart@gmail.com>
On Sun, 2 Jul 2006, Gene wrote:
> can use an index and perform as fast as searching with like '2345%'?
>
> Is the only way to create a reverse function and create an index using
> the reverse function and modify queries to use:
>
> where reverse(column) like reverse('%2345') ?
Hmm.. interesting.
If (and only if) the records stored in "column" column have fixed
length (say, all are 50 characters in length) you could create and index
on, say, substring(column,45,50), and use this in the WHERE clauses in
your queries.
Or if the length of those records is not the same maybe it is
feasible to create an ondex on substring(column, length(column)-5,
length(column)).
--
Any views or opinions presented within this e-mail are solely those of
the author and do not necessarily represent those of any company, unless
otherwise expressly stated.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: optimizing LIKE '%2345' queries
2006-07-02 21:50 optimizing LIKE '%2345' queries Gene <genekhart@gmail.com>
2006-07-04 11:14 ` Re: optimizing LIKE '%2345' queries Tarhon-Onu Victor <mituc@iasi.rdsnet.ro>
@ 2006-07-04 20:27 ` Gene <genekhart@gmail.com>
2006-07-05 00:22 ` Re: optimizing LIKE '%2345' queries Chris <dmagick@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gene @ 2006-07-04 20:27 UTC (permalink / raw)
To: Tarhon-Onu Victor <mituc@iasi.rdsnet.ro>; +Cc: pgsql-performance
Thanks for the suggestion. Actually I went ahead and created a reverse
function using plpgsql, created an index using reverse column and now
my queries use "where reverse(column) like reverse('%2345') and it's
using the index like i hoped it would! Now if I could figure out how
to optimize like '%2345%' queries. I don't want to create many
indexes though the table is very write heavy.
> > Is the only way to create a reverse function and create an index using
> > the reverse function and modify queries to use:
> >
> > where reverse(column) like reverse('%2345') ?
>
> Hmm.. interesting.
> If (and only if) the records stored in "column" column have fixed
> length (say, all are 50 characters in length) you could create and index
> on, say, substring(column,45,50), and use this in the WHERE clauses in
> your queries.
> Or if the length of those records is not the same maybe it is
> feasible to create an ondex on substring(column, length(column)-5,
> length(column)).
>
> --
> Any views or opinions presented within this e-mail are solely those of
> the author and do not necessarily represent those of any company, unless
> otherwise expressly stated.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: optimizing LIKE '%2345' queries
2006-07-02 21:50 optimizing LIKE '%2345' queries Gene <genekhart@gmail.com>
2006-07-04 11:14 ` Re: optimizing LIKE '%2345' queries Tarhon-Onu Victor <mituc@iasi.rdsnet.ro>
2006-07-04 20:27 ` Re: optimizing LIKE '%2345' queries Gene <genekhart@gmail.com>
@ 2006-07-05 00:22 ` Chris <dmagick@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Chris @ 2006-07-05 00:22 UTC (permalink / raw)
To: genekhart@gmail.com; +Cc: pgsql-performance
Gene wrote:
> Thanks for the suggestion. Actually I went ahead and created a reverse
> function using plpgsql, created an index using reverse column and now
> my queries use "where reverse(column) like reverse('%2345') and it's
> using the index like i hoped it would! Now if I could figure out how
> to optimize like '%2345%' queries. I don't want to create many
> indexes though the table is very write heavy.
You can't because that text can be anywhere inside the database field,
so the whole field basically has to be checked to see if it's there.
You could check out full text indexing (tsearch2).
<shameless plug>
http://www.designmagick.com/article/27/PostgreSQL/Introduction-to-Full-Text-Indexing
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-07-05 00:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-02 21:50 optimizing LIKE '%2345' queries Gene <genekhart@gmail.com>
2006-07-03 07:33 ` Mario Weilguni <mweilguni@sime.com>
2006-07-04 11:14 ` Tarhon-Onu Victor <mituc@iasi.rdsnet.ro>
2006-07-04 20:27 ` Gene <genekhart@gmail.com>
2006-07-05 00:22 ` Chris <dmagick@gmail.com>
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