Re: good style? - Mailing list pgsql-sql

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,
permissionp, 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
gON 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
acla 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_groupsu2g 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