Re: Best way to use indexes for partial match at beginning

On Wed, Nov 09, 2005 at 12:37:25PM +0200, Andrus wrote:
> I'm using Postgres 8.1 in Windows. Database encoding is UTF-8
> lc_ctype is Estonian_Estonia.1257.
> lc_collate is Estonian currently. However I can set lc_collate to C if this
> solves this issue.
>
> Doc says that
> " to allow PostgreSQL to use indexes with LIKE clauses under a non-C locale,
> several custom operator classes exist"
>
> I don't understand "non-C locale". Does this mean lc_collate or also some
> other lc_ setting ?

lc == locale. There are several different locale settings but collation
affects ordering. And Estonian is not C (obviously).

> I need to optimize queries with variable number of characters in beginning
> like
>
> SELECT ... WHERE substring( col1 for 1 ) = 'f'
> SELECT ... WHERE substring( col1 for 2 ) = 'fo'
> SELECT ... WHERE substring( col1 for 3 ) = 'foo'
> etc

If you use queries like:

SELECT ... WHERE col1 LIKE 'fo%'

it can use an index declared like:

CREATE INDEX myindex on mytable(col1 text_pattern_ops);

> In my current dbms, Microsoft Visual FoxPro I have a single index
>
> CREATE INDEX i1 ON mytable(col1)
>
> I can use queries:
>
> WHERE col1 BETWEEN 'f' and 'f'+CHR(255)
> WHERE col1 BETWEEN 'fo' and 'fo'+CHR(255)
> WHERE col1 BETWEEN 'foo' and 'foo'+CHR(255)

Well, you could do that in postgresql too, you just need to use the SQL
standard concatination operator.

WHERE col1 BETWEEN 'f' and 'f' || chr(255);

> How to get same functionality in Postgres ?
> Does there exist unicode special character which is greater than all other
> chars ?

Umm, I don't think so. Order is defined by the locale, not the
character set. My guess is that text_pattern_ops is the way to go.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.