Обсуждение: partial TEXT search on an index

I don't think I understand how PG implements fulltext searching or if my
search actually needs to use fulltext search.

basically, I have a btree index on a SERIAL_NUMBER which is of sort like
ABC12345 or AAA123434 or AAB131441

I would like to have search on the specific text of the SERIAL_NUMBER

eg:
select * from table where serial_number LIKE 'AAA%'

does not use the index. What Do i need to do to have it recognise the
"partial index?" (is it called?)

Thanks

Hi,

Ow Mun Heng schrieb:
> I don't think I understand how PG implements fulltext searching or if my
> search actually needs to use fulltext search.

No, that's something different. See the fine manual.

> basically, I have a btree index on a SERIAL_NUMBER which is of sort like
> ABC12345 or AAA123434 or AAB131441
>
> I would like to have search on the specific text of the SERIAL_NUMBER
>
> eg:
> select * from table where serial_number LIKE 'AAA%'

Depending on the PostgreSQL version and locale you are using chances
are, that you need to create another index on that column to make the
query use an index. C.f.

   http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html

CREATE INDEX another_index ON your_table (serial_number
varchar_pattern_ops);

> does not use the index. What Do i need to do to have it recognise the
> "partial index?" (is it called?)

No, that's not a partial index (look at e.g. wiki for an explanation).

Ciao,
Thomas

On Tue, 31 Mar 2009, Thomas Pundt wrote:

> Hi,
>
> Ow Mun Heng schrieb:
>> I don't think I understand how PG implements fulltext searching or if my
>> search actually needs to use fulltext search.
>
> No, that's something different. See the fine manual.

in 8.4 it will be possible to use prefix search in full-text search,
eg,  to_tsquery('AAA:*')

>
>> basically, I have a btree index on a SERIAL_NUMBER which is of sort like
>> ABC12345 or AAA123434 or AAB131441
>>
>> I would like to have search on the specific text of the SERIAL_NUMBER
>> eg:
>> select * from table where serial_number LIKE 'AAA%'
>
> Depending on the PostgreSQL version and locale you are using chances
> are, that you need to create another index on that column to make the
> query use an index. C.f.
>
>  http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html
>
> CREATE INDEX another_index ON your_table (serial_number varchar_pattern_ops);
>
>> does not use the index. What Do i need to do to have it recognise the
>> "partial index?" (is it called?)
>
> No, that's not a partial index (look at e.g. wiki for an explanation).
>
> Ciao,
> Thomas
>
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83