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

Well, for starters, see if PostgreSQL is currently using any indexes via
EXPLAIN. First rule of performance tuning: don't.

If it's not (which is probably the case), then your best bet is to
create functional indexes; ie:

CREATE INDEX mytable__col1_4 ON mytable( substring( col1 for 4 ) );

You can then either

SELECT ... WHERE substring( col1 for 4 ) = blah

or

SELECT ... WHERE substring( col1 for 4 ) LIKE 'bla%'

Though that last one might not use the index; you'll have to check and
see.

Also, keep in mind that PostgreSQL doesn't store CHAR the same as most
other databases; the internal storage is the same as what's used for
VARCHAR and TEXT.

On Sun, Nov 06, 2005 at 11:03:01PM +0200, Andrus Moor wrote:
> I have 10 CHARACTER columns in table mytable. Mytable is indexed by some of
> those columns.
>
> CREATE TABLE mytable ( col1 CHARACTER(10),
> col2 CHARACTER(10),col3 CHARACTER(10),col4 CHARACTER(10),col5
> CHARACTER(10),col6 CHARACTER(10),col7 CHARACTER(10),col8 CHARACTER(10),col9
> CHARACTER(10), col10 CHARACTER(10) );
>
> CREATE INDEX i1 ON mytable(col1);
> CREATE INDEX i2 ON mytable(col2);
>
> I need to select records by knowing some characters from beginning.
> I know always 1-10 first characters of col1. So my LIKE pattern starts
> always with constant characters and ends with % .
>
> I can use LIKE:
>
> SELECT * FROM mytable
> WHERE col1 LIKE 'A%'
> AND col2 LIKE 'BC%'
> AND col3 LIKE 'DEF%'
> AND col4 LIKE 'G%';
>
> or substring():
>
> SELECT * FROM mytable
> WHERE substring(col1 for 1)='A'
> AND substring(col2 for 2)= 'BC'
> AND substring(col3 for 3)='DEF'
> AND substring(col4 for 1) ='G';
>
>
> Can Postgres 8.1 use indexes to speed the queries above ?
>
> Which is the best way to to write the where clause in this case so that
> index is used ?
>
> Andrus.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461