pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedString matching
8+ messages / 3 participants
[nested] [flat]
* String matching
@ 2005-02-14 19:57 Ivan Voras <ivoras@fer.hr>
0 siblings, 1 reply; 8+ messages in thread
From: Ivan Voras @ 2005-02-14 19:57 UTC (permalink / raw)
To: pgsql-performance
Is there a way to use indexes for queries like:
select field from table where field like 'abc%'
(i.e. filter for string fields that begin with something) ?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: String matching
@ 2005-02-14 20:08 PFC <lists@boutiquenumerique.com>
parent: Ivan Voras <ivoras@fer.hr>
0 siblings, 1 reply; 8+ messages in thread
From: PFC @ 2005-02-14 20:08 UTC (permalink / raw)
To: Ivan Voras <ivoras@fer.hr>; pgsql-performance
normally you shouldn't have to do anything, it should just work :
> select field from table where field like 'abc%'
CREATE INDEX ... ON table( field );
that's all
If it does not use the index, I saw on the mailing list that the locale
could be an issue.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: String matching
@ 2005-02-14 20:31 Ivan Voras <ivoras@fer.hr>
parent: PFC <lists@boutiquenumerique.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ivan Voras @ 2005-02-14 20:31 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; +Cc: pgsql-performance
PFC wrote:
>
> normally you shouldn't have to do anything, it should just work :
>
>> select field from table where field like 'abc%'
> If it does not use the index, I saw on the mailing list that the
> locale could be an issue.
Oh yes, I forgot about that :( I do have LC_COLLATE (on latin2)...
It's a shame PostgreSQL doesn't allow collation rules on specific fields
- this field I'm using here will always be 7bit ASCII :(
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: String matching
@ 2005-02-14 20:42 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Ivan Voras <ivoras@fer.hr>
0 siblings, 1 reply; 8+ messages in thread
From: Stephan Szabo @ 2005-02-14 20:42 UTC (permalink / raw)
To: Ivan Voras <ivoras@fer.hr>; +Cc: PFC <lists@boutiquenumerique.com>; pgsql-performance
On Mon, 14 Feb 2005, Ivan Voras wrote:
> PFC wrote:
> >
> > normally you shouldn't have to do anything, it should just work :
> >
> >> select field from table where field like 'abc%'
>
> > If it does not use the index, I saw on the mailing list that the
> > locale could be an issue.
>
> Oh yes, I forgot about that :( I do have LC_COLLATE (on latin2)...
>
> It's a shame PostgreSQL doesn't allow collation rules on specific fields
> - this field I'm using here will always be 7bit ASCII :(
You can also create an index using a <typename>_pattern_ops operator
class which should be usable even with other collations.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: String matching
@ 2005-02-14 20:45 Ivan Voras <ivoras@fer.hr>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ivan Voras @ 2005-02-14 20:45 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: PFC <lists@boutiquenumerique.com>; pgsql-performance
Stephan Szabo wrote:
> You can also create an index using a <typename>_pattern_ops operator
> class which should be usable even with other collations.
Could you give me an example for this, or point me to the relevant
documentation?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: String matching
@ 2005-02-14 22:12 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Ivan Voras <ivoras@fer.hr>
0 siblings, 1 reply; 8+ messages in thread
From: Stephan Szabo @ 2005-02-14 22:12 UTC (permalink / raw)
To: Ivan Voras <ivoras@fer.hr>; +Cc: PFC <lists@boutiquenumerique.com>; pgsql-performance
On Mon, 14 Feb 2005, Ivan Voras wrote:
> Stephan Szabo wrote:
>
> > You can also create an index using a <typename>_pattern_ops operator
> > class which should be usable even with other collations.
>
> Could you give me an example for this, or point me to the relevant
> documentation?
Basically, you could have something like:
create table test_table(a text);
create index test_index on test_table(a text_pattern_ops);
------------------------------------------------------------------
http://www.postgresql.org/docs/8.0/interactive/indexes-opclass.html
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: String matching
@ 2005-02-14 22:29 Ivan Voras <ivoras@fer.hr>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ivan Voras @ 2005-02-14 22:29 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: PFC <lists@boutiquenumerique.com>; pgsql-performance
Stephan Szabo wrote:
> On Mon, 14 Feb 2005, Ivan Voras wrote:
>>Could you give me an example for this, or point me to the relevant
>>documentation?
>
> http://www.postgresql.org/docs/8.0/interactive/indexes-opclass.html
Thanks! I didn't know this and I certainly didn't think it would be that
easy :)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: String matching
@ 2005-02-14 22:31 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Ivan Voras <ivoras@fer.hr>
0 siblings, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2005-02-14 22:31 UTC (permalink / raw)
To: Ivan Voras <ivoras@fer.hr>; +Cc: PFC <lists@boutiquenumerique.com>; pgsql-performance
On Mon, 14 Feb 2005, Ivan Voras wrote:
> Stephan Szabo wrote:
> > On Mon, 14 Feb 2005, Ivan Voras wrote:
>
> >>Could you give me an example for this, or point me to the relevant
> >>documentation?
>
> >
> > http://www.postgresql.org/docs/8.0/interactive/indexes-opclass.html
>
> Thanks! I didn't know this and I certainly didn't think it would be that
> easy :)
Well, it's not perfect. It requires a separate index from one for normal
comparisons, so it's trading modification speed for LIKE lookup speed.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-02-14 22:31 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-14 19:57 String matching Ivan Voras <ivoras@fer.hr>
2005-02-14 20:08 ` PFC <lists@boutiquenumerique.com>
2005-02-14 20:31 ` Ivan Voras <ivoras@fer.hr>
2005-02-14 20:42 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-02-14 20:45 ` Ivan Voras <ivoras@fer.hr>
2005-02-14 22:12 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-02-14 22:29 ` Ivan Voras <ivoras@fer.hr>
2005-02-14 22:31 ` Stephan Szabo <sszabo@megazone.bigpanda.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