Обсуждение: 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