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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
