Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question about foreign key

Question about foreign key

From
Terence Leung
Date:
Dear sir,
    Hi, I am Terence and using Postgresql 7.0 in linux
server and the web server can connect the database.
    I want to ask one question:
I have created two tables
1.
CREATE TABLE sys_user (
 id  serial primary key,
 login_name  varchar(32),
 password  varchar(32)
)

2.
CREATE TABLE sys_quest_list (
 id     serial primary key,
 owner_id    int4,
 quest_name  varchar(100)
)

I add a foreign to sys_quest_list

ALTER TABLE sys_quest_list ADD CONSTRAINT sqloifk
foreign key(owner_id) REFERENCES sys_user(id);

The sys_quest_list.owner_id is the foreign key to
sys_user.id

When I do the select SQL like this
 "select login_name,quest_name FROM sys_quest_list
JOIN sys_user ON owner_id=sys_user.id"

Can the added foreign key improve the above sql
performance so that it can search faster
   or the foreign key can only maintain the data
integrity but cannot make the search faster?

  Your help would be much appreciated.
  Looking forward to your reply.

   Thank you very much



=====
Yours sincerely,
    Leung Chun Man, Terence

    Mobile: (852) 9273 9176
    Homepage:  http://tcmleung.uhome.net ,
    http://www.dynamicdrive.com/dynamicindex4/filter/index.htm

__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/

Re: Question about foreign key

From
Christoph Dalitz
Date:
> Date: Wed, 6 Nov 2002 03:49:49 -0800 (PST)
> From: Terence Leung <tcmleung@yahoo.com>
>
>     Hi, I am Terence and using Postgresql 7.0 in linux
>
You should consider an upgrade to 7.2 or (if you can wait some months)
to 7.3. These versions are *strong* improvements.

>  "select login_name,quest_name FROM sys_quest_list
> JOIN sys_user ON owner_id=sys_user.id"
>
> Can the added foreign key improve the above sql
> performance so that it can search faster
>
No.

>   or the foreign key can only maintain the data
> integrity but cannot make the search faster?
>
Yes.

For better search performance, do a CREATE INDEX and don't forget to
ANALYZE the table.

Christoph Dalitz

Re: Question about foreign key

From
Andrew Sullivan
Date:
On Wed, Nov 06, 2002 at 03:49:49AM -0800, Terence Leung wrote:
> Dear sir,
>     Hi, I am Terence and using Postgresql 7.0 in linux
> server and the web server can connect the database.

You'll be wanting to upgrade that.  7.0 is old and has had several
bugs fixed.

> Can the added foreign key improve the above sql
> performance so that it can search faster
>    or the foreign key can only maintain the data
> integrity but cannot make the search faster?

It is only an integrity contraint.  It makes no difference to speed
(well, that's not true.  It slows some other things down).

Is you are having a performance problem, all the usual questions
(vacuums, &c) apply.  But do upgrade first.  Among other things,
performance was much improved in 7.2.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110