pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgood style?
8+ messages / 5 participants
[nested] [flat]
* good style?
@ 2003-02-21 13:30 Rafal Kedziorski <rafcio@polonium.de>
2003-02-21 13:51 ` Re: good style? Rajesh Kumar Mallah <mallah@trade-india.com>
2003-02-21 13:54 ` Re: good style? Alan Gutierrez <ajglist@izzy.net>
0 siblings, 2 replies; 8+ messages in thread
From: Rafal Kedziorski @ 2003-02-21 13:30 UTC (permalink / raw)
To: pgsql-sql
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?
Best Regards,
Rafal
sql script:
DROP TABLE groups_2_acl_entry;
DROP TABLE users_2_groups;
DROP TABLE groups;
DROP TABLE users;
DROP TABLE acl_entry;
DROP TABLE permission;
DROP TABLE acl;
DROP TABLE language;
DROP TABLE mandant;
DROP TABLE license;
DROP TABLE firm;
CREATE TABLE firm (
firm_id numeric(20, 0) NOT NULL,
name varchar(40) NOT NULL,
CONSTRAINT firm_pkey PRIMARY KEY (firm_id)
) WITH OIDS;
INSERT INTO firm VALUES (1, 'polonium');
CREATE TABLE license (
license_id numeric(20, 0) NOT NULL,
key varchar(100) NOT NULL,
creation_date timestamp NOT NULL,
valid_from timestamp NOT NULL,
expired timestamp,
CONSTRAINT license_pkey PRIMARY KEY (license_id)
) WITH OIDS;
INSERT INTO license VALUES (1, 'NOT AT THIS TIME - SHOULD BE GENERATED',
now(), now(), NULL);
CREATE TABLE mandant (
mandant_id numeric(20, 0) NOT NULL,
firm_id numeric(20, 0) NOT NULL,
license_id numeric(20, 0) NOT NULL,
parent_id numeric(20, 0),
name varchar(20) NOT NULL,
creation_date timestamp NOT NULL,
CONSTRAINT mandant_pkey PRIMARY KEY (mandant_id),
CONSTRAINT fk_firm FOREIGN KEY (firm_id) REFERENCES firm (firm_id),
CONSTRAINT fk_license FOREIGN KEY (license_id) REFERENCES license
(license_id),
CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES mandant
(mandant_id)
) WITH OIDS;
INSERT INTO mandant VALUES (1, 1, 1, NULL, 'polonium', now());
CREATE TABLE language (
language_id int2 NOT NULL,
lang_short char(2) NOT NULL,
lang_long varchar(20) NOT NULL,
CONSTRAINT language_pkey PRIMARY KEY (language_id)
) WITH OIDS;
CREATE UNIQUE INDEX language_lang_short_idx ON language (lang_short);
CREATE UNIQUE INDEX language_lang_idx ON language (lang_short, lang_long);
INSERT INTO language VALUES (1, 'de', 'deutsch');
INSERT INTO language VALUES (2, 'en', 'english');
CREATE TABLE acl (
acl_id int2 NOT NULL,
name varchar(20) NOT NULL,
description varchar(200),
CONSTRAINT acl_pkey PRIMARY KEY (acl_id)
) WITH OIDS;
CREATE UNIQUE INDEX acl_name_idx ON acl (name);
INSERT INTO acl VALUES (1, 'mmcms.access', 'acl for login module');
INSERT INTO acl VALUES (2, 'mmcms.system', 'acl for system module');
INSERT INTO acl VALUES (3, 'mmcms.admin', 'acl for admin module');
INSERT INTO acl VALUES (4, 'mmcms.category', 'acl for category module');
INSERT INTO acl VALUES (5, 'mmcms.context', 'acl for context module');
CREATE TABLE permission (
permission_id int2 NOT NULL,
name varchar(20) NOT NULL,
description varchar(200),
CONSTRAINT permission_pkey PRIMARY KEY (permission_id)
) WITH OIDS;
CREATE UNIQUE INDEX permission_name_idx ON permission (name);
INSERT INTO permission VALUES (1, 'access', 'access permission');
INSERT INTO permission VALUES (2, 'read', 'read permission');
INSERT INTO permission VALUES (3, 'write', 'write permission');
INSERT INTO permission VALUES (4, 'execute', 'execute permission');
INSERT INTO permission VALUES (5, 'modify', 'modify permission');
INSERT INTO permission VALUES (6, 'list', 'list permission');
CREATE TABLE acl_entry (
acl_entry_id int2 NOT NULL,
acl_id int2 NOT NULL,
permission_id int2 NOT NULL,
CONSTRAINT acl_entry_pkey PRIMARY KEY (acl_entry_id),
CONSTRAINT fk_acl FOREIGN KEY (acl_id) REFERENCES acl (acl_id),
CONSTRAINT fk_permission FOREIGN KEY (permission_id) REFERENCES
permission (permission_id)
) WITH OIDS;
-- acl_entry for mmcms.access acl with access permission
-- acl 'mmcms.access' has 'access' permission
INSERT INTO acl_entry VALUES (1, 1, 1);
-- acl 'mmcms.system' has 'read' permission
INSERT INTO acl_entry VALUES (2, 2, 2);
-- acl 'mmcms.system' has 'write' permission
INSERT INTO acl_entry VALUES (3, 2, 3);
-- acl 'mmcms.admin' has 'read' permission
INSERT INTO acl_entry VALUES (4, 3, 2);
-- acl 'mmcms.admin' has 'write' permission
INSERT INTO acl_entry VALUES (5, 3, 3);
CREATE TABLE users (
users_id numeric(20, 0) NOT NULL,
mandant_id numeric(20, 0) NOT NULL,
language_id int2 NOT NULL,
login_name varchar(50) NOT NULL,
password varchar(15) NOT NULL,
first_name varchar(20) NOT NULL,
last_name varchar(20) NOT NULL,
creation_date timestamp NOT NULL,
last_login_date timestamp,
status int2 NOT NULL,
CONSTRAINT users_pkey PRIMARY KEY (users_id),
CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant
(mandant_id),
CONSTRAINT fk_language FOREIGN KEY (language_id) REFERENCES language
(language_id)
) WITH OIDS;
CREATE UNIQUE INDEX users_login_name_idx ON users (login_name);
INSERT INTO users VALUES (1, 1, 1, 'rafcio@polonium.de', 'test',
'Rafal', 'Kedziorski', now(), NULL, 0);
CREATE TABLE groups (
groups_id numeric(20, 0) NOT NULL,
mandant_id numeric(20, 0) NOT NULL,
name varchar(20) NOT NULL,
description varchar(200) NOT NULL,
creation_date timestamp NOT NULL,
CONSTRAINT groups_pkey PRIMARY KEY (groups_id),
CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant
(mandant_id)
) WITH OIDS;
CREATE UNIQUE INDEX groups_name_idx ON groups (mandant_id, name);
-- every mandant should have own access group
INSERT INTO groups VALUES (1, 1, 'access', 'access group', now());
INSERT INTO groups VALUES (2, 1, 'system', 'system group', now());
INSERT INTO groups VALUES (3, 1, 'admin', 'admin group', now());
CREATE TABLE users_2_groups (
users_2_groups_id numeric(20, 0) NOT NULL,
users_id numeric(20, 0) NOT NULL,
groups_id numeric(20, 0) NOT NULL,
valid_from timestamp NOT NULL,
expired timestamp,
CONSTRAINT users_2_groups_pkey PRIMARY KEY (users_2_groups_id),
CONSTRAINT fk_groups FOREIGN KEY (groups_id) REFERENCES groups
(groups_id),
CONSTRAINT fk_users FOREIGN KEY (users_id) REFERENCES users (users_id)
) WITH OIDS;
CREATE UNIQUE INDEX users_2_groups_usersgroups__idx ON users_2_groups
(users_id, groups_id);
INSERT INTO users_2_groups VALUES (1, 1, 1, now(), NULL);
INSERT INTO users_2_groups VALUES (2, 1, 2, now(), NULL);
INSERT INTO users_2_groups VALUES (3, 1, 3, now(), NULL);
CREATE TABLE groups_2_acl_entry (
groups_2_acl_entry_id numeric(20, 0) NOT NULL,
groups_id numeric(20, 0) NOT NULL,
acl_entry_id int2 NOT NULL,
CONSTRAINT groups_2_acl_entry_pkey PRIMARY KEY (groups_2_acl_entry_id),
CONSTRAINT fk_groups FOREIGN KEY (groups_id) REFERENCES groups
(groups_id),
CONSTRAINT fk_acl_entry FOREIGN KEY (acl_entry_id) REFERENCES
acl_entry (acl_entry_id)
) WITH OIDS;
INSERT INTO groups_2_acl_entry VALUES (1, 1, 1);
INSERT INTO groups_2_acl_entry VALUES (2, 2, 2);
INSERT INTO groups_2_acl_entry VALUES (3, 2, 3);
INSERT INTO groups_2_acl_entry VALUES (4, 3, 4);
INSERT INTO groups_2_acl_entry VALUES (5, 3, 5);
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: good style?
2003-02-21 13:30 good style? Rafal Kedziorski <rafcio@polonium.de>
@ 2003-02-21 13:51 ` Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 8+ messages in thread
From: Rajesh Kumar Mallah @ 2003-02-21 13:51 UTC (permalink / raw)
To: Rafal Kedziorski <rafcio@polonium.de>; pgsql-sql
this kind of joining has been termed "explicit JOIN syntax"
by pgsql 7.3 docs.
I personally feel it makes ur SQL look uglier and complicated. i feel the
WHERE caluse shud contain the genuine filters of result set not the ones
which could be a part of JOIN syntax itself. (its personal view though)
you may refer to the DOCs below on postgresqls' website.
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=explicit-joins.html
..... excerpt from the docs.........
When a query only involves two or three tables, there aren't many join orders
to worry about. But the number of possible join orders grows exponentially as
the number of tables expands. Beyond ten or so input tables it's no longer
practical to do an exhaustive search of all the possibilities, and even for
six or seven tables planning may take an annoyingly long time. When there are
too many input tables, the PostgreSQL planner will switch from exhaustive
search to a genetic probabilistic search through a limited number of
possibilities. (The switch-over threshold is set by the GEQO_THRESHOLD
run-time parameter described in the PostgreSQL 7.3 Administrator's Guide.)
The genetic search takes less time, but it won't necessarily find the best
possible plan.
............
regds
mallah.
On Friday 21 February 2003 07:00 pm, Rafal Kedziorski wrote:
> 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?
>
>
> Best Regards,
> Rafal
>
>
> sql script:
>
> DROP TABLE groups_2_acl_entry;
> DROP TABLE users_2_groups;
> DROP TABLE groups;
> DROP TABLE users;
> DROP TABLE acl_entry;
> DROP TABLE permission;
> DROP TABLE acl;
> DROP TABLE language;
> DROP TABLE mandant;
> DROP TABLE license;
> DROP TABLE firm;
>
>
>
> CREATE TABLE firm (
> firm_id numeric(20, 0) NOT NULL,
> name varchar(40) NOT NULL,
> CONSTRAINT firm_pkey PRIMARY KEY (firm_id)
> ) WITH OIDS;
>
> INSERT INTO firm VALUES (1, 'polonium');
>
>
>
> CREATE TABLE license (
> license_id numeric(20, 0) NOT NULL,
> key varchar(100) NOT NULL,
> creation_date timestamp NOT NULL,
> valid_from timestamp NOT NULL,
> expired timestamp,
> CONSTRAINT license_pkey PRIMARY KEY (license_id)
> ) WITH OIDS;
>
> INSERT INTO license VALUES (1, 'NOT AT THIS TIME - SHOULD BE GENERATED',
> now(), now(), NULL);
>
>
>
> CREATE TABLE mandant (
> mandant_id numeric(20, 0) NOT NULL,
> firm_id numeric(20, 0) NOT NULL,
> license_id numeric(20, 0) NOT NULL,
> parent_id numeric(20, 0),
> name varchar(20) NOT NULL,
> creation_date timestamp NOT NULL,
> CONSTRAINT mandant_pkey PRIMARY KEY (mandant_id),
> CONSTRAINT fk_firm FOREIGN KEY (firm_id) REFERENCES firm (firm_id),
> CONSTRAINT fk_license FOREIGN KEY (license_id) REFERENCES license
> (license_id),
> CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES mandant
> (mandant_id)
> ) WITH OIDS;
>
> INSERT INTO mandant VALUES (1, 1, 1, NULL, 'polonium', now());
>
>
>
> CREATE TABLE language (
> language_id int2 NOT NULL,
> lang_short char(2) NOT NULL,
> lang_long varchar(20) NOT NULL,
> CONSTRAINT language_pkey PRIMARY KEY (language_id)
> ) WITH OIDS;
>
> CREATE UNIQUE INDEX language_lang_short_idx ON language (lang_short);
> CREATE UNIQUE INDEX language_lang_idx ON language (lang_short, lang_long);
>
> INSERT INTO language VALUES (1, 'de', 'deutsch');
> INSERT INTO language VALUES (2, 'en', 'english');
>
>
>
> CREATE TABLE acl (
> acl_id int2 NOT NULL,
> name varchar(20) NOT NULL,
> description varchar(200),
> CONSTRAINT acl_pkey PRIMARY KEY (acl_id)
> ) WITH OIDS;
>
> CREATE UNIQUE INDEX acl_name_idx ON acl (name);
>
> INSERT INTO acl VALUES (1, 'mmcms.access', 'acl for login module');
> INSERT INTO acl VALUES (2, 'mmcms.system', 'acl for system module');
> INSERT INTO acl VALUES (3, 'mmcms.admin', 'acl for admin module');
> INSERT INTO acl VALUES (4, 'mmcms.category', 'acl for category module');
> INSERT INTO acl VALUES (5, 'mmcms.context', 'acl for context module');
>
>
>
> CREATE TABLE permission (
> permission_id int2 NOT NULL,
> name varchar(20) NOT NULL,
> description varchar(200),
> CONSTRAINT permission_pkey PRIMARY KEY (permission_id)
> ) WITH OIDS;
>
> CREATE UNIQUE INDEX permission_name_idx ON permission (name);
>
> INSERT INTO permission VALUES (1, 'access', 'access permission');
> INSERT INTO permission VALUES (2, 'read', 'read permission');
> INSERT INTO permission VALUES (3, 'write', 'write permission');
> INSERT INTO permission VALUES (4, 'execute', 'execute permission');
> INSERT INTO permission VALUES (5, 'modify', 'modify permission');
> INSERT INTO permission VALUES (6, 'list', 'list permission');
>
>
>
> CREATE TABLE acl_entry (
> acl_entry_id int2 NOT NULL,
> acl_id int2 NOT NULL,
> permission_id int2 NOT NULL,
> CONSTRAINT acl_entry_pkey PRIMARY KEY (acl_entry_id),
> CONSTRAINT fk_acl FOREIGN KEY (acl_id) REFERENCES acl (acl_id),
> CONSTRAINT fk_permission FOREIGN KEY (permission_id) REFERENCES
> permission (permission_id)
> ) WITH OIDS;
>
> -- acl_entry for mmcms.access acl with access permission
>
> -- acl 'mmcms.access' has 'access' permission
> INSERT INTO acl_entry VALUES (1, 1, 1);
> -- acl 'mmcms.system' has 'read' permission
> INSERT INTO acl_entry VALUES (2, 2, 2);
> -- acl 'mmcms.system' has 'write' permission
> INSERT INTO acl_entry VALUES (3, 2, 3);
> -- acl 'mmcms.admin' has 'read' permission
> INSERT INTO acl_entry VALUES (4, 3, 2);
> -- acl 'mmcms.admin' has 'write' permission
> INSERT INTO acl_entry VALUES (5, 3, 3);
>
>
>
> CREATE TABLE users (
> users_id numeric(20, 0) NOT NULL,
> mandant_id numeric(20, 0) NOT NULL,
> language_id int2 NOT NULL,
> login_name varchar(50) NOT NULL,
> password varchar(15) NOT NULL,
> first_name varchar(20) NOT NULL,
> last_name varchar(20) NOT NULL,
> creation_date timestamp NOT NULL,
> last_login_date timestamp,
> status int2 NOT NULL,
> CONSTRAINT users_pkey PRIMARY KEY (users_id),
> CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant
> (mandant_id),
> CONSTRAINT fk_language FOREIGN KEY (language_id) REFERENCES language
> (language_id)
> ) WITH OIDS;
>
> CREATE UNIQUE INDEX users_login_name_idx ON users (login_name);
>
> INSERT INTO users VALUES (1, 1, 1, 'rafcio@polonium.de', 'test',
> 'Rafal', 'Kedziorski', now(), NULL, 0);
>
>
>
> CREATE TABLE groups (
> groups_id numeric(20, 0) NOT NULL,
> mandant_id numeric(20, 0) NOT NULL,
> name varchar(20) NOT NULL,
> description varchar(200) NOT NULL,
> creation_date timestamp NOT NULL,
> CONSTRAINT groups_pkey PRIMARY KEY (groups_id),
> CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant
> (mandant_id)
> ) WITH OIDS;
>
> CREATE UNIQUE INDEX groups_name_idx ON groups (mandant_id, name);
>
> -- every mandant should have own access group
> INSERT INTO groups VALUES (1, 1, 'access', 'access group', now());
> INSERT INTO groups VALUES (2, 1, 'system', 'system group', now());
> INSERT INTO groups VALUES (3, 1, 'admin', 'admin group', now());
>
>
>
> CREATE TABLE users_2_groups (
> users_2_groups_id numeric(20, 0) NOT NULL,
> users_id numeric(20, 0) NOT NULL,
> groups_id numeric(20, 0) NOT NULL,
> valid_from timestamp NOT NULL,
> expired timestamp,
> CONSTRAINT users_2_groups_pkey PRIMARY KEY (users_2_groups_id),
> CONSTRAINT fk_groups FOREIGN KEY (groups_id) REFERENCES groups
> (groups_id),
> CONSTRAINT fk_users FOREIGN KEY (users_id) REFERENCES users (users_id)
> ) WITH OIDS;
>
> CREATE UNIQUE INDEX users_2_groups_usersgroups__idx ON users_2_groups
> (users_id, groups_id);
>
> INSERT INTO users_2_groups VALUES (1, 1, 1, now(), NULL);
> INSERT INTO users_2_groups VALUES (2, 1, 2, now(), NULL);
> INSERT INTO users_2_groups VALUES (3, 1, 3, now(), NULL);
>
>
>
> CREATE TABLE groups_2_acl_entry (
> groups_2_acl_entry_id numeric(20, 0) NOT NULL,
> groups_id numeric(20, 0) NOT NULL,
> acl_entry_id int2 NOT NULL,
> CONSTRAINT groups_2_acl_entry_pkey PRIMARY KEY (groups_2_acl_entry_id),
> CONSTRAINT fk_groups FOREIGN KEY (groups_id) REFERENCES groups
> (groups_id),
> CONSTRAINT fk_acl_entry FOREIGN KEY (acl_entry_id) REFERENCES
> acl_entry (acl_entry_id)
> ) WITH OIDS;
>
> INSERT INTO groups_2_acl_entry VALUES (1, 1, 1);
> INSERT INTO groups_2_acl_entry VALUES (2, 2, 2);
> INSERT INTO groups_2_acl_entry VALUES (3, 2, 3);
> INSERT INTO groups_2_acl_entry VALUES (4, 3, 4);
> INSERT INTO groups_2_acl_entry VALUES (5, 3, 5);
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Regds
Mallah
----------------------------------------
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: good style?
2003-02-21 13:30 good style? Rafal Kedziorski <rafcio@polonium.de>
@ 2003-02-21 13:54 ` Alan Gutierrez <ajglist@izzy.net>
1 sibling, 0 replies; 8+ messages in thread
From: Alan Gutierrez @ 2003-02-21 13:54 UTC (permalink / raw)
To: pgsql-sql
In article <3E5629EE.6000406@polonium.de>, Rafal Kedziorski wrote:
> 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?
Better style, yes. Whitespace would help also.
Faster, maybe. If you use join clauses you will be able to take control
over your query, specifying what gets joined when.
--
Alan Gutierrez - ajglist@izzy.net
http://khtml-win32.sourceforge.net/ - KHTML on Windows
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: good style?
@ 2003-02-21 14:39 Tambet Matiisen <t.matiisen@aprote.ee>
2003-02-21 16:05 ` Re: good style? Rafal Kedziorski <rafcio@polonium.de>
2003-02-23 23:51 ` Re: good style? Rafal Kedziorski <rafcio@polonium.de>
0 siblings, 2 replies; 8+ messages in thread
From: Tambet Matiisen @ 2003-02-21 14:39 UTC (permalink / raw)
To: Rafal Kedziorski <rafcio@polonium.de>; pgsql-sql
> -----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.
Tambet
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: good style?
2003-02-21 14:39 Re: good style? Tambet Matiisen <t.matiisen@aprote.ee>
@ 2003-02-21 16:05 ` Rafal Kedziorski <rafcio@polonium.de>
1 sibling, 0 replies; 8+ messages in thread
From: Rafal Kedziorski @ 2003-02-21 16:05 UTC (permalink / raw)
To: Tambet Matiisen <t.matiisen@aprote.ee>; +Cc: pgsql-sql
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.
>
I would genarete more test data and compare my first version with the
new version.
Rafal
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: good style?
2003-02-21 14:39 Re: good style? Tambet Matiisen <t.matiisen@aprote.ee>
@ 2003-02-23 23:51 ` Rafal Kedziorski <rafcio@polonium.de>
2003-02-25 06:42 ` Re: good style? Tambet Matiisen <tambet.matiisen@mail.ee>
1 sibling, 1 reply; 8+ messages in thread
From: Rafal Kedziorski @ 2003-02-23 23:51 UTC (permalink / raw)
To: 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, 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: good style?
2003-02-21 14:39 Re: good style? Tambet Matiisen <t.matiisen@aprote.ee>
2003-02-23 23:51 ` Re: good style? Rafal Kedziorski <rafcio@polonium.de>
@ 2003-02-25 06:42 ` Tambet Matiisen <tambet.matiisen@mail.ee>
2003-02-25 08:44 ` Re: good style? Rafal Kedziorski <rafcio@polonium.de>
0 siblings, 1 reply; 8+ messages in thread
From: Tambet Matiisen @ 2003-02-25 06:42 UTC (permalink / raw)
To: Tambet Matiisen <t.matiisen@aprote.ee>; pgsql-sql; Rafal Kedziorski <rafcio@polonium.de>
----- Original Message -----
From: "Rafal Kedziorski" <rafcio@polonium.de>
To: "Tambet Matiisen" <t.matiisen@aprote.ee>; <pgsql-sql@postgresql.org>
Sent: Monday, February 24, 2003 1:51 AM
Subject: Re: [SQL] good style?
> [snip]
>
>
> 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?
>
>
Optimizer expects the original query to return 1000 rows, while others are
expected to return 5000 rows. I compared the original query with others, but
didn't see any difference at first sight. I don't know, if the expected row
count depends on execution path in Postgres. You can look at explain output
of original query and imitate the join order chosen by optimizer by JOINs,
and then compare costs.
What are the real-world timings? And do these queries actually return the
same result? If you calculate cost for fetching one row, then 3184,29 / 5000
= 0,636858, while 820.47 / 1000 = 0,82047. So maybe it's not that bad at
all.
Tambet
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: good style?
2003-02-21 14:39 Re: good style? Tambet Matiisen <t.matiisen@aprote.ee>
2003-02-23 23:51 ` Re: good style? Rafal Kedziorski <rafcio@polonium.de>
2003-02-25 06:42 ` Re: good style? Tambet Matiisen <tambet.matiisen@mail.ee>
@ 2003-02-25 08:44 ` Rafal Kedziorski <rafcio@polonium.de>
0 siblings, 0 replies; 8+ messages in thread
From: Rafal Kedziorski @ 2003-02-25 08:44 UTC (permalink / raw)
To: Tambet Matiisen <tambet.matiisen@mail.ee>; +Cc: pgsql-sql
Tambet Matiisen wrote:
>----- Original Message -----
>From: "Rafal Kedziorski" <rafcio@polonium.de>
>To: "Tambet Matiisen" <t.matiisen@aprote.ee>; <pgsql-sql@postgresql.org>
>Sent: Monday, February 24, 2003 1:51 AM
>Subject: Re: [SQL] good style?
>
>
>
>
>>[snip]
>>
>>
>>original:
>>
>>Merge Join (cost=728.47..820.47 rows=1000 width=366)
>>...
>>
>>
Merge Join (cost=728.47..820.47 rows=1000 width=366)
Merge Cond: ("outer".groups_id = "inner".groups_id)
-> Sort (cost=435.32..437.82 rows=1000 width=80)
Sort Key: g2ae.groups_id
-> Merge Join (cost=313.49..385.49 rows=1000 width=80)
Merge Cond: ("outer".permission_id = "inner".permission_id)
-> Index Scan using permission_pkey on permission p
(cost=0.00..52.00 rows=1000 width=26)
-> Sort (cost=313.49..315.99 rows=1000 width=54)
Sort Key: ae.permission_id
-> Merge Join (cost=191.66..263.66 rows=1000 width=54)
Merge Cond: ("outer".acl_id = "inner".acl_id)
-> Index Scan using acl_pkey on acl a
(cost=0.00..52.00 rows=1000 width=26)
-> Sort (cost=191.66..194.16 rows=1000 width=28)
Sort Key: ae.acl_id
-> Merge Join (cost=69.83..141.83
rows=1000 width=28)
Merge Cond: ("outer".acl_entry_id
= "inner".acl_entry_id)
-> Index Scan using
acl_entry_pkey on acl_entry ae (cost=0.00..52.00 rows=1000 width=6)
-> Sort (cost=69.83..72.33
rows=1000 width=22)
Sort Key: g2ae.acl_entry_id
-> Seq Scan on
groups_2_acl_entry g2ae (cost=0.00..20.00 rows=1000 width=22)
-> Materialize (cost=365.16..365.16 rows=1000 width=286)
-> Merge Join (cost=293.16..365.16 rows=1000 width=286)
Merge Cond: ("outer".groups_id = "inner".groups_id)
-> Index Scan using groups_pkey on groups g
(cost=0.00..52.00 rows=1000 width=44)
-> Sort (cost=293.16..295.66 rows=1000 width=242)
Sort Key: u2g.groups_id
-> Merge Join (cost=171.33..243.33 rows=1000
width=242)
Merge Cond: ("outer".mandant_id =
"inner".mandant_id)
-> Index Scan using mandant_pkey on mandant
m (cost=0.00..52.00 rows=1000 width=44)
-> Sort (cost=171.33..173.83 rows=1000
width=198)
Sort Key: u.mandant_id
-> Merge Join (cost=0.00..121.50
rows=1000 width=198)
Merge Cond: ("outer".users_id =
"inner".users_id)
-> Index Scan using
users_2_groups_usersgroups__idx on users_2_groups u2g (cost=0.00..52.00
rows=1000 width=40)
-> Index Scan using users_pkey on
users u (cost=0.00..52.00 rows=1000 width=158)
>>
>>
>>1st join:
>>Merge Join (cost=3042.29..3184.29 rows=5000 width=366)
>>...
>>
Merge Join (cost=3042.29..3184.29 rows=5000 width=366)
Merge Cond: ("outer".permission_id = "inner".permission_id)
-> Index Scan using permission_pkey on permission p
(cost=0.00..52.00 rows=1000 width=26)
-> Sort (cost=3042.29..3054.79 rows=5000 width=340)
Sort Key: ae.permission_id
-> Merge Join (cost=2131.70..2273.70 rows=5000 width=340)
Merge Cond: ("outer".acl_id = "inner".acl_id)
-> Index Scan using acl_pkey on acl a (cost=0.00..52.00
rows=1000 width=26)
-> Sort (cost=2131.70..2144.20 rows=5000 width=314)
Sort Key: ae.acl_id
-> Merge Join (cost=1253.25..1395.25 rows=5000
width=314)
Merge Cond: ("outer".acl_entry_id =
"inner".acl_entry_id)
-> Index Scan using acl_entry_pkey on
acl_entry ae (cost=0.00..52.00 rows=1000 width=6)
-> Sort (cost=1253.25..1265.75 rows=5000
width=308)
Sort Key: g2ae.acl_entry_id
-> Merge Join (cost=383.32..525.32
rows=5000 width=308)
Merge Cond: ("outer".groups_id =
"inner".groups_id)
-> Merge Join
(cost=313.49..385.49 rows=1000 width=286)
Merge Cond:
("outer".groups_id = "inner".groups_id)
-> Index Scan using
groups_pkey on groups g (cost=0.00..52.00 rows=1000 width=44)
-> Sort
(cost=313.49..315.99 rows=1000 width=242)
Sort Key: u2g.groups_id
-> Merge Join
(cost=191.66..263.66 rows=1000 width=242)
Merge Cond:
("outer".users_id = "inner".users_id)
-> Index Scan
using users_2_groups_usersgroups__idx on users_2_groups u2g
(cost=0.00..52.00 rows=1000 width=40)
-> Sort
(cost=191.66..194.16 rows=1000 width=202)
Sort Key:
u.users_id
-> Merge
Join (cost=69.83..141.83 rows=1000 width=202)
Merge Cond: ("outer".mandant_id = "inner".mandant_id)
->
Index Scan using mandant_pkey on mandant m (cost=0.00..52.00 rows=1000
width=44)
->
Sort (cost=69.83..72.33 rows=1000 width=158)
Sort Key: u.mandant_id
-> Seq Scan on users u (cost=0.00..20.00 rows=1000 width=158)
-> Sort (cost=69.83..72.33
rows=1000 width=22)
Sort Key: g2ae.groups_id
-> Seq Scan on
groups_2_acl_entry g2ae (cost=0.00..20.00 rows=1000 width=22)
>Optimizer expects the original query to return 1000 rows, while others are
>expected to return 5000 rows. I compared the original query with others, but
>didn't see any difference at first sight. I don't know, if the expected row
>count depends on execution path in Postgres. You can look at explain output
>of original query and imitate the join order chosen by optimizer by JOINs,
>and then compare costs.
>
>What are the real-world timings? And do these queries actually return the
>same result? If you calculate cost for fetching one row, then 3184,29 / 5000
>= 0,636858, while 820.47 / 1000 = 0,82047. So maybe it's not that bad at
>all.
>
> Tambet
>
Rafal
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-02-25 08:44 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-21 13:30 good style? Rafal Kedziorski <rafcio@polonium.de>
2003-02-21 13:51 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-02-21 13:54 ` Alan Gutierrez <ajglist@izzy.net>
2003-02-21 14:39 Re: good style? Tambet Matiisen <t.matiisen@aprote.ee>
2003-02-21 16:05 ` Rafal Kedziorski <rafcio@polonium.de>
2003-02-23 23:51 ` Rafal Kedziorski <rafcio@polonium.de>
2003-02-25 06:42 ` Tambet Matiisen <tambet.matiisen@mail.ee>
2003-02-25 08:44 ` Rafal Kedziorski <rafcio@polonium.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox