From: | "Andrus Moor" <eetasoft(at)online(dot)ee> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Best way to use indexes for partial match at beginning |
Date: | 2005-11-06 21:03:01 |
Message-ID: | dklr9d$781$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus Moor | 2005-11-06 21:03:43 | CREATE TEMP TABLE AS ... ON COMMIT DROP fails |
Previous Message | Marc G. Fournier | 2005-11-06 20:35:24 | Re: Question about 8.1 release news |