How to use index in strpos function

Поиск
Список
Период
Сортировка
От Tuan Hoang Anh
Тема How to use index in strpos function
Дата
Msg-id 495AF9C1.8030203@gmail.com
обсуждение исходный текст
Ответы Re: How to use index in strpos function  ("Adam Rich" <adam.r@sbcglobal.net>)
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: "disappearing" rows in temp table, in recursing trigger
Следующее
От: "Adam Rich"
Дата:
Сообщение: Re: How to use index in strpos function