473,249 Members | 1,878 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,249 software developers and data experts.

Noobie: Problems with a query

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
0 1202

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: carverk | last post by:
Hello All I'm in the middle of moving a MS Access DB to a MySql backend. I have figured out about 90% of the problems I have faced, execpt for this one. I have 3 Queries, which pull records...
2
by: osgnamah | last post by:
Hi All; I am getting ready to launch a shareware program in the next few weeks and the last step is going to be creating a website. So the last few days I've been doing some web surfing in...
11
by: (Pete Cresswell) | last post by:
I'm about to start climbing the .Net learning curve. Would like to start by reproducing a little application that I already have up and running using an MS Access front end. It manages...
6
by: Aristotelis E. Charalampakis | last post by:
Hi all, this is a newbie question :-) I was wondering if there was a way to use the switch statement in a manner that each case statement includes more that a simple value. i.e.: switch (...
2
by: megafrenzy | last post by:
I've have an idea for a personal project. I want to access my car's OBD-II port via serial to query engine paramters and represent them on my PC monitor. The various methods to represent them would...
1
by: Chad | last post by:
Hi, I am a SQL Server programmer using Oracle for the first time. In our .NET client apps which use a SQL Server back end, we would use Stored Procedure exclusively for all database access for...
3
by: maharg | last post by:
Hi, I have a small application that displays an html file locally from the hard drive. Using Navigate2, I would like to launch the html from the same directory using a relative path. Right...
1
by: jobs | last post by:
I've got a class with a new constructor Sub New(ByVal connstring As String) p_cnn = New SqlConnection(connstring) p_cnn.Open() End Sub I instatiate it like this in my ssis script task vb.net...
3
by: qazplm114477 | last post by:
lets say i have 1 table called Inventory and another table called itemType Inventory -itemID -PK -Desc -itemID- FK itemType -ItemID...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.