Re: good style? - Mailing list pgsql-sql

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

>  
>