left join + case - how is it processed?

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/