pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex to support LIKE '%suffix' queries
6+ messages / 4 participants
[nested] [flat]
* Index to support LIKE '%suffix' queries
@ 2006-02-25 14:17 Florian Weimer <fw@deneb.enyo.de>
0 siblings, 1 reply; 6+ messages in thread
From: Florian Weimer @ 2006-02-25 14:17 UTC (permalink / raw)
To: pgsql-sql
Is it possible to create an index to support queries of the form
"column LIKE '%suffix'" (similar to an ordinary index for LIKE
'prefix%', which I also need)?
I could define a function which reverts strings (or revert them in the
application) and use a normal B-tree index, but I wonder if there is a
better way.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index to support LIKE '%suffix' queries
@ 2006-02-25 17:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Florian Weimer <fw@deneb.enyo.de>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2006-02-25 17:22 UTC (permalink / raw)
To: Florian Weimer <fw@deneb.enyo.de>; +Cc: pgsql-sql
Florian Weimer <fw@deneb.enyo.de> writes:
> Is it possible to create an index to support queries of the form
> "column LIKE '%suffix'" (similar to an ordinary index for LIKE
> 'prefix%', which I also need)?
Sounds like what you *really* need is full-text search, not half
measures ... have you looked at tsearch2?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index to support LIKE '%suffix' queries
@ 2006-02-25 17:52 Florian Weimer <fw@deneb.enyo.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Florian Weimer @ 2006-02-25 17:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
* Tom Lane:
> Florian Weimer <fw@deneb.enyo.de> writes:
>> Is it possible to create an index to support queries of the form
>> "column LIKE '%suffix'" (similar to an ordinary index for LIKE
>> 'prefix%', which I also need)?
>
> Sounds like what you *really* need is full-text search, not half
> measures ... have you looked at tsearch2?
Uh-oh, the table in question has got 50+ million rows (and is still
growing). Each "document" contains about three words. Do you think
tsearch2 could deal with that?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index to support LIKE '%suffix' queries
@ 2006-02-25 18:13 Alvaro Herrera <alvherre@commandprompt.com>
parent: Florian Weimer <fw@deneb.enyo.de>
0 siblings, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2006-02-25 18:13 UTC (permalink / raw)
To: Florian Weimer <fw@deneb.enyo.de>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
> > Florian Weimer <fw@deneb.enyo.de> writes:
> >> Is it possible to create an index to support queries of the form
> >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE
> >> 'prefix%', which I also need)?
It is possible to create a functional index on the reverse of the
string. You need to also reverse the patter at query-time as well.
This will make the % be at the end of the pattern, making it an
indexable condition.
Whether or not this beats tsearch2 is something you should investigate ...
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index to support LIKE '%suffix' queries
@ 2006-03-01 07:19 Florian Weimer <fw@deneb.enyo.de>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 6+ messages in thread
From: Florian Weimer @ 2006-03-01 07:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
* Alvaro Herrera:
>> > Florian Weimer <fw@deneb.enyo.de> writes:
>> >> Is it possible to create an index to support queries of the form
>> >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE
>> >> 'prefix%', which I also need)?
>
> It is possible to create a functional index on the reverse of the
> string.
Okay. Is there a predefined reverse function? I couldn't find one
and I'm wondering if I just missed it.
> Whether or not this beats tsearch2 is something you should investigate ...
It's also possible that for this type of query, sequential scans are
good enough. I forgot that they are quite fast.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index to support LIKE '%suffix' queries
@ 2006-03-01 11:33 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Florian Weimer <fw@deneb.enyo.de>
0 siblings, 0 replies; 6+ messages in thread
From: A. Kretschmer @ 2006-03-01 11:33 UTC (permalink / raw)
To: pgsql-sql
am 01.03.2006, um 8:19:40 +0100 mailte Florian Weimer folgendes:
> * Alvaro Herrera:
>
> >> > Florian Weimer <fw@deneb.enyo.de> writes:
> >> >> Is it possible to create an index to support queries of the form
> >> >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE
> >> >> 'prefix%', which I also need)?
> >
> > It is possible to create a functional index on the reverse of the
> > string.
>
> Okay. Is there a predefined reverse function? I couldn't find one
> and I'm wondering if I just missed it.
simple to write one, http://a-kretschmer.de/diverses.shtml
HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-03-01 11:33 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-25 14:17 Index to support LIKE '%suffix' queries Florian Weimer <fw@deneb.enyo.de>
2006-02-25 17:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-25 17:52 ` Florian Weimer <fw@deneb.enyo.de>
2006-02-25 18:13 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-03-01 07:19 ` Florian Weimer <fw@deneb.enyo.de>
2006-03-01 11:33 ` A. Kretschmer <andreas.kretschmer@schollglas.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