Re: How to use index in strpos function

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Tuan Hoang Anh
> Sent: Tuesday, December 30, 2008 10:49 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] How to use index in strpos function
>
> I have table command
> CREATE TABLE command
> And one index
>
> CREATE INDEX command_command
> ON command
> USING btree(upper(command));
>
> And have a query use it
> explain select * from command where strpos('APCTPN1.EXE PN1',
> UPPER(command)) > 0 AND UPPER(command) <> ''
> "Seq Scan on command (cost=100000000.00..100000015.26 rows=92
> width=200)"
> " Filter: ((upper((command)::text) <> ''::text) AND
> (strpos('APCTPN1.EXE PN1'::text, upper((command)::text)) > 0))"
>
> This command is called a lot, so i want to use index in it. How to use
> index on this command
>

Is the first argument to strops always the same ('APCTPN1.EXE PN1') ?
if so, you can create an index like this:

CREATE INDEX strpos_command
ON command
USING (strpos('APCTPN1.EXE PN1', UPPER(command.command)))

However, if the argument is different for each query, then you will
not be able to utilize an functional index for this type of query.

If you mean to query for "starts with" you can rewrite your query as:

select * from command
where UPPER(command.command) LIKE 'APCTPN1.EXE PN1%'

However, if you mean to query for "contains substring" then a regular
index or functional index will not help. A full-text index might help,
but it is more complex to setup and use. The documentation for that
is here: http://www.postgresql.org/docs/8.3/interactive/textsearch.html