On Sun, Jan 18, 2009 at 9:30 PM, Chris <dmagick@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
