Re: good style?

Tambet Matiisen wrote:

>----- Original Message -----
>From: "Rafal Kedziorski" <rafcio(at)polonium(dot)de>
>To: "Tambet Matiisen" <t(dot)matiisen(at)aprote(dot)ee>; <pgsql-sql(at)postgresql(dot)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

>
>