Re: good style?

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

> > -----Original Message-----
> > From: Rafal Kedziorski [mailto:rafcio(at)polonium(dot)de]
> > Sent: Friday, February 21, 2003 3:30 PM
> > To: pgsql-sql(at)postgresql(dot)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