help with getting index scan

From: "Thomas T(dot) Thai" <tom(at)minnesota(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: help with getting index scan
Date: 2002-02-23 22:10:22
Message-ID: Pine.NEB.4.43.0202231600570.21797-100000@ns01.minnesota.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

i can't seem to get index scan to work on table phone_cat_address. 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2002-02-24 00:33:47 Re: game db
Previous Message Bruce Momjian 2002-02-23 20:11:44 Re: How to Run postgres inside gdb for debugging