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/
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
