left join + case - how is it processed? - Mailing list pgsql-performance

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/