Re: Foreign keys? - Mailing list pgsql-general

It was a little bit late when I wrote that, and so I
probably should have been a little more specific.  I
don't know if you would notice a performance
difference between the joined tables query and and the
non-joined version for such simple tables.  I might
have to spend a bit of time today loading a test
database with sufficient data to test it, because now
I am curious.

However, I know that if your tables are more involved
than the trivial ones that I included that it can make
a big difference.  This is especially true if you want
to join a table to several lookup tables.  In those
cases it is a serious performance win to have the data
in the master table and simply use the lookup tables
to guarantee that valid data is entered.

By the time you have a query that looks like this:

SELECT users.name, states.name, institutions.name,
divisions.name, trucks.id from users, states,
institutions, divisions, trucks WHERE users.state =
states.id AND users.institution = institutions.id AND
users.division = divisions.id AND users.truck =
trucks.id AND users.id = 'MYID';

PostgreSQL is going to wish that you had put more of
that information in the users table.  A view might
make the query easier to type, but it won't undo the
performance penalty of multiple joins.

At least that is how I understand it.  I might be
wrong, however, I never have pretended to be a SQL
guru, but I certainly noticed a performance difference
when I switched from a table with multiple joins to
one with more of the information directly in the table
(it still referenced primary keys in another table,
they just were varchar primary keys and not ints).

Jason

--- Richard Huxton <dev@archonet.com> wrote:
> From: "Jason Earl" <jdearl@yahoo.com>
>
> > However, if you are going to do a lot of joins on
> your
> > user table along the lines of:
> >
> > SELECT user.name, object.description FROM user,
> object
> > WHERE user.number = object.owner;
> >
> > Then you might be better off simplifying just a
> bit to
> > give you something like:
> >
> > CREATE TABLE user (
> >        name VARCHAR(400) PRIMARY KEY
> > );
> >
> > CREATE TABLE object (
> >        owner VARCHAR(400) REFERENCES user NOT
> NULL,
> >        description VARCHAR(200)
> > );
> >
> > That would save you having to join the table to
> find
> > the user.name at the expense of using more hard
> drive
> > space.
>
> I'm curious - are you speaking from a performance
> viewpoint here, or just
> about simplifying queries (in which case I'd just
> slap a view on top)?
>
> - Richard Huxton
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/