pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
String 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