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