Re: [SQL] good style?

[Rafal Kedziorski]

Tambet Matiisen wrote:

----- Original Message -----
From: "Rafal Kedziorski" <[EMAIL PROTECTED]>
To: "Tambet Matiisen" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
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

 



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])