X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 808AC475CE7 for ; Tue, 25 Feb 2003 05:49:23 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 71160-01 for ; Tue, 25 Feb 2003 05:49:12 -0500 (EST) Received: from trill.maridan.net (unknown [217.6.52.210]) by postgresql.org (Postfix) with ESMTP id 92095475FEC for ; Tue, 25 Feb 2003 03:43:18 -0500 (EST) Received: from polonium.de ([212.121.156.162]) by trill.maridan.net (8.9.3/8.9.3) with ESMTP id JAA32421; Tue, 25 Feb 2003 09:43:16 +0100 Message-ID: <3E5B2D06.2000109@polonium.de> Date: Tue, 25 Feb 2003 09:44:54 +0100 From: Rafal Kedziorski Organization: POLONIUM User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.2.1) Gecko/20021130 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tambet Matiisen Cc: pgsql-sql@postgresql.org Subject: Re: good style? References: <5.2.0.9.0.20030224004643.01b21e08@mail.polonium.de> <002601c2dc99$056ef520$0100a8c0@kodunet.ee> In-Reply-To: <002601c2dc99$056ef520$0100a8c0@kodunet.ee> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/543 X-Sequence-Number: 11941 Tambet Matiisen wrote: >----- Original Message ----- >From: "Rafal Kedziorski" >To: "Tambet Matiisen" ; >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 > >