Thread: good style?

good style?

From
Rafal Kedziorski
Date:
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,
permissionp, 
 
acl a, acl_entry ae, groups_2_acl_entry g2ae where m.mandant_id = u.mandant_id and            u2g.groups_id =
g.groups_idand            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_fromtimestamp 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)
NOTNULL, parent_id numeric(20, 0), name varchar(20) NOT NULL, creation_date timestamp NOT NULL, CONSTRAINT mandant_pkey
PRIMARYKEY (mandant_id), CONSTRAINT fk_firm FOREIGN KEY (firm_id) REFERENCES firm (firm_id), CONSTRAINT fk_license
FOREIGNKEY (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,
CONSTRAINTlanguage_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
PRIMARYKEY (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_pkeyPRIMARY 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_pkeyPRIMARY KEY (acl_entry_id), CONSTRAINT fk_acl FOREIGN KEY (acl_id) REFERENCES acl (acl_id), CONSTRAINT
fk_permissionFOREIGN 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_namevarchar(50) NOT NULL, password varchar(15) NOT NULL, first_name varchar(20) NOT NULL, last_name varchar(20)
NOTNULL, creation_date timestamp NOT NULL, last_login_date timestamp, status int2 NOT NULL, CONSTRAINT users_pkey
PRIMARYKEY (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,
descriptionvarchar(200) NOT NULL, creation_date timestamp NOT NULL, CONSTRAINT groups_pkey PRIMARY KEY (groups_id),
CONSTRAINTfk_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_idint2 NOT NULL, CONSTRAINT groups_2_acl_entry_pkey PRIMARY KEY (groups_2_acl_entry_id), CONSTRAINT fk_groups
FOREIGNKEY (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);




Re: good style?

From
Rajesh Kumar Mallah
Date:
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.


Re: good style?

From
Alan Gutierrez
Date:
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


Re: good style?

From
"Tambet Matiisen"
Date:

> -----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
ifyou 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
optimizerto decide. Generally you know better what tables will contain more rows and what less. It's more important in
developmentphase, 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,
andyou sometimes filter by field in table1, sometimes by field in table3. When you fix join order by using JOINS then
oneof the queries may perform bad. When you join tables in WHERE, the optimizer chooses whether it should join table1
andtable2 first or table3 and table2 first. The former is better when filtering by field in table1, the latter is
betterwhen filtering by field in table3. 
 Tambet


Re: good style?

From
Rafal Kedziorski
Date:
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
betterif 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
optimizerto decide. Generally you know better what tables will contain more rows and what less. It's more important in
developmentphase, 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



Re: good style?

From
Rafal Kedziorski
Date:
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 



Re: good style?

From
Rafal Kedziorski
Date:
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.82rows=1000 width=80)       Sort Key: g2ae.groups_id       ->  Merge Join  (cost=313.49..385.49
rows=1000width=80)             Merge Cond: ("outer".permission_id = "inner".permission_id)             ->  Index Scan
usingpermission_pkey on permission p  
 
(cost=0.00..52.00 rows=1000 width=26)             ->  Sort  (cost=313.49..315.99 rows=1000 width=54)
SortKey: 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)
SortKey: 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) ->
IndexScan 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

>  
>



Re: good style?

From
"Tambet Matiisen"
Date:
----- 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