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
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 |