Best way to use indexes for partial match at beginning

Поиск
Список
Период
Сортировка
От Andrus Moor
Тема Best way to use indexes for partial match at beginning
Дата
Msg-id dklr9d$781$1@news.hub.org
обсуждение исходный текст
Ответы Re: Best way to use indexes for partial match at beginning
Список 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.



В списке pgsql-general по дате отправления:

Предыдущее
От: "Marc G. Fournier"
Дата:
Сообщение: Re: Question about 8.1 release news
Следующее
От: "Andrus Moor"
Дата:
Сообщение: CREATE TEMP TABLE AS ... ON COMMIT DROP fails