Thread: How to use index in strpos function

I have table command
CREATE TABLE command
(
  menuid0 character varying(20) NOT NULL DEFAULT ''::character varying,
  menuid character varying(20) NOT NULL DEFAULT ''::character varying,
  "text" character varying NOT NULL DEFAULT ''::character varying,
  text2 character varying NOT NULL DEFAULT ''::character varying,
  ma_ct character(3) NOT NULL DEFAULT ''::bpchar,
  report character varying NOT NULL DEFAULT ''::character varying,
  command character varying NOT NULL DEFAULT ''::character varying,
  title character varying NOT NULL DEFAULT ''::character varying,
  title2 character varying NOT NULL DEFAULT ''::character varying,
  basicright smallint NOT NULL DEFAULT 0,
  picture1 character varying NOT NULL DEFAULT ''::character varying,
  picture2 character varying NOT NULL DEFAULT ''::character varying,
  "type" character varying(2) NOT NULL DEFAULT ''::character varying,
  "sysid" character varying NOT NULL DEFAULT ''::character varying,
  syscode character varying NOT NULL DEFAULT ''::character varying,
  CONSTRAINT command_pkey PRIMARY KEY (menuid0)
)
WITH (OIDS=TRUE);
ALTER TABLE command OWNER TO postgres;

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

Thanks a lot.
Sorry for my English

Tuan Hoang Anh

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Tuan Hoang Anh
> Sent: Tuesday, December 30, 2008 10:49 PM
> To: pgsql-general@postgresql.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