Thread: 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, 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);
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.
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
> -----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
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
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
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
>
>
----- 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