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