Message-Id: <5.2.0.9.0.20030224004643.01b21e08@mail.polonium.de>
Date: Mon, 24 Feb 2003 00:51:08 +0100
To: "Tambet Matiisen" <t.matiisen@aprote.ee>,
	<pgsql-sql@postgresql.org>
From: Rafal Kedziorski <rafcio@polonium.de>
Subject: Re: good style?
In-Reply-To: <81132473206F3A46A72BD6116E1A06AE1B14E9@black.aprote.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

At 16:39 21.02.2003 +0200, Tambet Matiisen wrote:


> > -----Original Message-----
> > From: Rafal Kedziorski [mailto:rafcio@polonium.de]
> > Sent: Friday, February 21, 2003 3:30 PM
> > To: pgsql-sql@postgresql.org
> > Subject: [SQL] good style?
> >
> >
> > hi,
> >
> > I have 8 tables and this query:
> >
> > select u.users_id, m.name as mandant_name, u.login_name, u.password,
> > u.first_name, u.last_name, u.creation_date, g.name as groups_name,
> > ae.acl_entry_id, a.name as acl_name, p.name as permission_name
> >   from mandant m, users_2_groups u2g, groups g, users u,
> > permission p,
> > acl a, acl_entry ae, groups_2_acl_entry g2ae
> >   where m.mandant_id = u.mandant_id and
> >              u2g.groups_id = g.groups_id and
> >              u2g.users_id = u.users_id and
> >              g2ae.groups_id = g.groups_id and
> >              g2ae.acl_entry_id = ae.acl_entry_id and
> >              ae.acl_id = a.acl_id and
> >              ae.permission_id = p.permission_id
> >
> > I'm not using JOIN for get this information. would be JOIN a
> > better sql
> > programming style? faster?
> >
>
>As there is no outer join syntax to use in WHERE, you need to write LEFT 
>JOINs anyway. And in this case it looks better if you write all joins as 
>JOIN clauses.
>
>When using JOIN you are directing Postgres to use exactly this join order. 
>I found it preferrable over letting query optimizer to decide. Generally 
>you know better what tables will contain more rows and what less. It's 
>more important in development phase, because there is usually not much 
>test data and all tables look the same to optimizer.
>
>There are few cases, when it's better to join in WHERE. For example when 
>you have 3 tables, all joined sequentially, and you sometimes filter by 
>field in table1, sometimes by field in table3. When you fix join order by 
>using JOINS then one of the queries may perform bad. When you join tables 
>in WHERE, the optimizer chooses whether it should join table1 and table2 
>first or table3 and table2 first. The former is better when filtering by 
>field in table1, the latter is better when filtering by field in table3.

i tryed this:

original:

select u.users_id, m.name as mandant_name, u.login_name, u.password, 
u.first_name, u.last_name, u.creation_date, g.name as groups_name, 
ae.acl_entry_id, a.name as acl_name, p.name as permission_name
   from users u, mandant m, users_2_groups u2g, groups g, permission p, acl 
a, acl_entry ae, groups_2_acl_entry g2ae
   where m.mandant_id = u.mandant_id and
              u2g.groups_id = g.groups_id and
              u2g.users_id = u.users_id and
              g2ae.groups_id = g.groups_id and
              g2ae.acl_entry_id = ae.acl_entry_id and
              ae.acl_id = a.acl_id and
              ae.permission_id = p.permission_id;


1st join:

select u.users_id, m.name as mandant_name, u.login_name, u.password, 
u.first_name, u.last_name, u.creation_date, g.name as groups_name, 
ae.acl_entry_id, a.name as acl_name, p.name as permission_name
   from users u JOIN mandant m ON u.mandant_id = m.mandant_id
                       JOIN users_2_groups u2g ON u.users_id = u2g.users_id
                       JOIN groups g ON u2g.groups_id = g.groups_id
                       JOIN groups_2_acl_entry g2ae ON g.groups_id = 
g2ae.groups_id
                       JOIN acl_entry ae ON g2ae.acl_entry_id = ae.acl_entry_id
                       JOIN acl a ON ae.acl_id = a.acl_id
                       JOIN permission p ON ae.permission_id = p.permission_id


2nd join:

SELECT u.users_id, m.name as mandant_name, u.login_name, u.password, 
u.first_name, u.last_name, u.creation_date, g.name as groups_name, 
ae.acl_entry_id, a.name as acl_name, p.name as permission_name
   FROM users u CROSS JOIN mandant m CROSS JOIN users_2_groups u2g CROSS 
JOIN groups g CROSS JOIN groups_2_acl_entry g2ae CROSS JOIN acl_entry ae 
CROSS JOIN acl a CROSS JOIN permission p
   WHERE u.mandant_id = m.mandant_id AND u.users_id = u2g.users_id
                                                                  AND 
u2g.groups_id = g.groups_id
                                                                  AND 
g.groups_id = g2ae.groups_id
                                                                  AND 
g2ae.acl_entry_id = ae.acl_entry_id
                                                                  AND 
ae.acl_id = a.acl_id
                                                                  AND 
ae.permission_id = p.permission_id


and here explain:


original:

Merge Join  (cost=728.47..820.47 rows=1000 width=366)
...


1st join:
Merge Join  (cost=3042.29..3184.29 rows=5000 width=366)
...


2nd join:
Merge Join  (cost=3042.29..3184.29 rows=5000 width=366)
...


have I post thic correctly using JOIN?


Best Regards,
Rafal