Re: left join + case - how is it processed?

On Sun, Jan 18, 2009 at 9:30 PM, Chris <dmagick(at)gmail(dot)com> wrote:
> 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.

Lets assume it wasn't useless because of that, it would still be
obfuscated and probably slower because it is an explicit coalesce()

SELECT coalesce( r.assetid, p.assetid ) , coalesce( r.userid , p.userid )

> 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 ??
# explain analyze SELECT * from sq_vw_ast_perm where assetid='30748';
# explain analyze SELECT * from sq_vw_ast_perm where assetid='30748';

It aperas to me that both of your statements have where clauses, but I
believe where isn't that explicit. I'm not sure the nature of your
problem permits the query optimizer to eliminate rows at all, even
with the where statement. "assetid" is probably not known when the
query optimizer hits, because it is computed based on the nullness of
the columns. I'd assume that the optimizer *could* more easily
optimize this if you had used coalesce rather than an ad-hoc method
with CASE. My guess is you can exclude rows with WHERE if the the
column used is an run-time computation involving an ad-hoc CASE.

--
Evan Carroll
System Lord of the Internets