left join + case - how is it processed?

[Chris <dmagick@xxxxxxxxx>]

Hi all,

I have a view that looks like this:

 SELECT
        CASE
            WHEN r.assetid IS NULL THEN p.assetid
            ELSE r.assetid
        END AS assetid,
        CASE
            WHEN r.userid IS NULL THEN p.userid
            ELSE r.userid
        END AS userid, p.permission, p."granted", p.cascades
   FROM sq_ast_perm p
   LEFT JOIN sq_vw_ast_role r ON r.roleid::text = p.userid::text AND 
r.assetid::text = p.assetid::text;


It was pointed out to me that the first CASE is useless (since r.assetid 
will always be the same as p.assetid because of the left join condition) 
so I'm looking at that to see if it'll make much of a difference and it 
does.

I won't post the whole lot but the first line is the most interesting.

# explain analyze SELECT * from sq_vw_ast_perm where assetid='30748';


 Merge Left Join  (cost=9529.34..13823.76 rows=75721 width=102) (actual 
time=284.371..341.536 rows=1 loops=1)

(The row count is right - it's the total # of rows from sq_ast_perm).


When I change the view to be:

 SELECT p.assetid,
        CASE
            WHEN r.userid IS NULL THEN p.userid
            ELSE r.userid
        END AS userid, p.permission, p."granted", p.cascades
   FROM sq_ast_perm p
   LEFT JOIN sq_vw_ast_role r ON r.roleid::text = p.userid::text AND 
r.assetid::text = p.assetid::text;


The Merge left join only returns 3 rows:

# explain analyze SELECT * from sq_vw_ast_perm where assetid='30748';

           Merge Left Join  (cost=9507.18..9508.23 rows=3 width=70) 
(actual time=11.544..11.549 rows=1 loops=1)

I thought the where condition would cut down on the rows returned, then 
the case statement would take effect to do the null check. It seems to 
be doing it in reverse ??

Recently analyzed, only just imported so free of bloat. Running 8.1.11.

Thanks!
--
Postgresql & php tutorials
http://www.designmagick.com/


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance