By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,302 Members | 1,200 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,302 IT Pros & Developers. It's quick & easy.

Noobie: Problems with a query

P: n/a
The query that I'm trying to execute is as follows:

SELECT
card_names.card_name,
card_sets.set_name
FROM
card_names_in_sets,
card_names,
card_sets
WHERE
card_names_in_sets.card_name_record_num =
card_names.record_num
AND
card_names_in_sets.card_set_record_number =
card_sets.record_num;

And the explain for this query is:

Merge Join (cost=100.37..186.36 rows=191 width=83)
Merge Cond: ("outer".record_num = "inner".card_name_record_num)
-> Index Scan using card_names_record_num_idx on card_names
(cost=0.00..78.09 rows=1826 width=47)
-> Sort (cost=100.37..100.85 rows=191 width=36)
Sort Key: card_names_in_sets.card_name_record_num
-> Hash Join (cost=1.14..93.16 rows=191 width=36)
Hash Cond:
("outer".card_set_record_number = "inner".record_num)
-> Seq Scan on card_names_in_sets (cost=0.00..63.65
rows=3465 width=8)
-> Hash (cost=1.11..1.11 rows=11 width=28)
-> Seq Scan on card_sets (cost=0.00..1.11
rows=11 width=28)

which, sadly, is greek to me. The problem is that the above query
takes very close to 12 seconds to execute. Is there a better way I
could write the query? The number of rows in each table are as
follows:

1826 : card names
3465 : card_names_in_sets
11 : card_sets

On a side note, it takes almost 11 seconds just to display the 3500
rows in card_names_in_sets. Is there a better way to create that
table?

The table "card_names_in_sets" is a bridge between the tables
"card_names" and "card_sets".

My 3 tables are (from pgAdminII):
CREATE TABLE public.card_names_in_sets (
card_name_record_num int4 NOT NULL,
card_set_record_number int4 NOT NULL,
record_num int4
DEFAULT nextval('public.card_names_in_sets_record_num_seq' ::text)
NOT NULL,
CONSTRAINT card_names_in_sets_record_num_idx UNIQUE (record_num),
CONSTRAINT card_names_in_sets_pkey PRIMARY KEY (record_num),
CONSTRAINT "$1" FOREIGN KEY (card_name_record_num) REFERENCES
card_names (record_num) ON DELETE CASCADE ON UPDATE NO ACTION NOT
DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "$2" FOREIGN KEY (card_set_record_number) REFERENCES
card_sets (record_num) ON DELETE CASCADE ON UPDATE NO ACTION NOT
DEFERRABLE INITIALLY IMMEDIATE
) WITH OIDS;
CREATE UNIQUE INDEX card_names_in_sets_record_num_idx ON
card_names_in_sets USING btree (record_num);
CREATE INDEX card_names_in_sets_card_name_record_num_idx ON
card_names_in_sets USING btree (card_name_record_num);
CREATE INDEX card_names_in_sets_card_set_record_num_idx ON
card_names_in_sets USING btree (card_set_record_number);

CREATE TABLE public.card_names (
card_name varchar(50) DEFAULT '',
record_num int4
DEFAULT nextval('public.card_names_record_num_seq'::text) NOT NULL,
CONSTRAINT card_names_record_num_idx UNIQUE (record_num),
CONSTRAINT card_names_pkey PRIMARY KEY (record_num),
CONSTRAINT card_names_integrity CHECK (((card_name IS NOT NULL) AND
(card_name <> ''::character varying)))
) WITH OIDS;
CREATE UNIQUE INDEX card_names_record_num_idx ON card_names USING btree
(record_num);

CREATE TABLE public.card_sets (
set_name varchar(20) DEFAULT '',
record_num int4
DEFAULT nextval('public.card_sets_record_num_seq'::text) NOT NULL,
CONSTRAINT card_sets_record_num_idx UNIQUE (record_num),
CONSTRAINT card_sets_pkey PRIMARY KEY (record_num),
CONSTRAINT card_sets_integrity CHECK (((set_name IS NOT NULL) AND
(set_name <> ''::character varying)))
) WITH OIDS;
CREATE UNIQUE INDEX card_sets_record_num_idx ON card_sets USING btree
(record_num);

Any help with this would be *greatly* appreciated!!

thnx,
Christoh
Nov 11 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.