help with getting index scan
От | Thomas T. Thai |
---|---|
Тема | help with getting index scan |
Дата | |
Msg-id | Pine.NEB.4.43.0202241620550.24442-100000@ns01.minnesota.com обсуждение исходный текст |
Список | pgsql-general |
i can't seem to get index scan to work on table phone_cat_address. can anyone suggest a way to get index scan to work? here are my schemas: CREATE TABLE "phone_address" ( "id" integer DEFAULT nextval('"phone_address_id_seq"'::text) NOT NULL, "aid" bigint, "name" character varying(96), "address" character varying(60), ... "nameftx" txtidx ); CREATE UNIQUE INDEX phone_address_id_key ON phone_address USING btree (id); CREATE UNIQUE INDEX phone_address_aid_key ON phone_address USING btree (aid); CREATE INDEX phone_address_name_idx ON phone_address USING btree (lower(name)); CREATE INDEX phone_address_nameftx_idx ON phone_address USING gist (nameftx); CREATE TABLE "phone_cat" ( "id" integer DEFAULT nextval('"phone_cat_id_seq"'::text) NOT NULL, "cid" integer, "name" character varying(96), "popular" character(1) DEFAULT 'N', "nameftx" txtidx ); CREATE UNIQUE INDEX phone_cat_id_key ON phone_cat USING btree (id); CREATE UNIQUE INDEX phone_cat_cid_key ON phone_cat USING btree (cid); CREATE INDEX phone_cat_name_idx ON phone_cat USING btree (lower(name)); CREATE INDEX phone_cat_nameftx_idx ON phone_cat USING gist (nameftx); CREATE TABLE "phone_cat_address" ( "cid" integer NOT NULL, "aid" bigint NOT NULL ); CREATE UNIQUE INDEX phone_cat_address_cid_key ON phone_cat_address USING btree (cid, aid); ---- here is the explain: yellowpages=# explain SELECT p.name,p.address,p.city,p.state yellowpages-# FROM phone_address AS p, phone_cat AS pFROM phone_address AS p, phone_cat AS pc, ph one_cat_address AS pca yellowpages-# WHERE pc.nameftx ## 'automobile&repair' AND pc.cid=pca.cid AND pca.aid=p.aid yellowpages-# ; NOTICE: QUERY PLAN: Nested Loop (cost=44.12..9272.76 rows=337 width=83) -> Hash Join (cost=44.12..7243.86 rows=337 width=16) -> Seq Scan on phone_cat_address pca (cost=0.00..5512.02 rows=336702 width=12) -> Hash (cost=44.09..44.09 rows=11 width=4) -> Index Scan using phone_cat_nameftx_idx on phone_cat pc (cost=0.00..44.09 rows= 11 width=4) -> Index Scan using phone_address_aid_key on phone_address p (cost=0.00..6.01 rows=1 width=67 ) NOTICE: QUERY PLAN: Nested Loop (cost=44.12..9272.76 rows=337 width=83) -> Hash Join (cost=44.12..7243.86 rows=337 width=16) -> Seq Scan on phone_cat_address pca (cost=0.00..5512.02 rows=336702 width=12) -> Hash (cost=44.09..44.09 rows=11 width=4) -> Index Scan using phone_cat_nameftx_idx on phone_cat pc (cost=0.00..44.09 rows= 11 width=4) -> Index Scan using phone_address_aid_key on phone_address p (cost=0.00..6.01 rows=1 width=67 ) EXPLAIN
В списке pgsql-general по дате отправления: