Re: good style?
От | Rafal Kedziorski |
---|---|
Тема | Re: good style? |
Дата | |
Msg-id | 5.2.0.9.0.20030224004643.01b21e08@mail.polonium.de обсуждение исходный текст |
Ответ на | Re: good style? ("Tambet Matiisen" <t.matiisen@aprote.ee>) |
Список | 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
В списке pgsql-sql по дате отправления: