How to use index in strpos function

From: Tuan Hoang Anh <hatuan05(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to use index in strpos function
Date: 2008-12-31 04:49:05
Message-ID: 495AF9C1.8030203@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adam Rich 2008-12-31 05:11:01 Re: How to use index in strpos function
Previous Message Tom Lane 2008-12-30 22:55:14 Re: "disappearing" rows in temp table, in recursing trigger