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

> The reason why the CASE is affecting your query planning is because
> you are using a query that compares assetid to a constant:
>
> SELECT * from sq_vw_ast_perm where assetid='30748';
>
> When PostgreSQL evaluates this statement, assetid gets expanded either
> into a case statement (with your first view definition) or into
> sq_ast_perm.assetid (with your second view definition).  The latter
> definition allows PostgreSQL to make use of the column statistics
> (which are pretty accurate) whereas the former is probably leading to
> a SWAG, because PostgreSQL isn't very good at estimating the
> selectivity of CASE.  The bad selectivity estimate, in turn, is
> leading to a poor plan choice...

If I take it out of the view, it's fine:

#  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
# where p.assetid='30748';

           QUERY PLAN

---------------------------------------------
  Merge Left Join  (cost=9459.89..9463.13 rows=3 width=102) (actual
time=0.096..0.098 rows=1 loops=1)

In this case I assume the planner is doing the 'WHERE' first to cut down
the rows, then applying the CASE at the end.

The view it seems to be the opposite - I still don't understand why
that's the case.


Though I do get the same behaviour as the view when I do it as a subselect.

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