help with getting index scan - Mailing list pgsql-general

From Thomas T. Thai
Subject help with getting index scan
Date
Msg-id Pine.NEB.4.43.0202241620550.24442-100000@ns01.minnesota.com
Whole thread Raw
List 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 by date:

Previous
From: Hugo Coolens
Date:
Subject: no database files found.
Next
From: weh8spam@aol.com (Weh8spam)
Date:
Subject: install psql 7.1.3 - rh6.2 - libreadline.so.4