View performance

I was looking at some queries that appeared to be slower than I remembered
them being under 7.2 (which may be a wrong perception) and noticed
that a view wasn't being handled very efficiently.

The view is security view that is used to hide some fields in some records
when displaying information on the web. The primary key is left alone
though. When this view is joined a plan is generated that applies
the field suppression for each row of the underlying table even though
only a few rows out of this view are going to be selected. It would see
that first looking for rows that will be used and only applying the
changes to rows that are going to be used would result in a significant
speed up.

The other thing that seemed odd is that the constant
(select pord from priv where pname = 'web') subqueries weren't pulled
out of the loop.

I was able to get a 20% speed up by adding an index on gameid to crate
and by disabling merge joins so that a has join was used instead.
The merge join estimate was about 20% low and the hash join estimate
was about 100% high resulting in the merge join getting picked.

View:
create view cname_web as select
areaid,
case when (select pord from priv where pname = 'web') >=
(select pord from priv where pname = privacy) then
lname else null end as lname,
case when (select pord from priv where pname = 'web') >=
(select pord from priv where pname = privacy) then
fmname else null end as fmname,
case when (select pord from priv where pname = 'web') >=
(select pord from priv where pname = privacy) then
aname else null end as aname,
case when (select pord from priv where pname = 'web') >=
(select pord from priv where pname = privacy) then
gen else null end as gen,
case when (select pord from priv where pname = 'web') >=
(select pord from priv where pname = privacy) then
genlab else null end as genlab,
case when (select pord from priv where pname = 'web') >=
(select pord from priv where pname = privacy) then
touched else null end as touched
from cname;

Query:

explain analyze select cname_web.areaid, lname, fmname, aname, coalesce(genlab, to_char(gen, 'FMRN')), rate, frq, opp, rmp, trn, to_char(crate.touched,'YYYY-MM-DD') from cname_web, crate where cname_web.areaid = crate.areaid and gameid = '776' and frq > 0 and crate.touched >= ((timestamp 'epoch' + '1040733601 second') + '2 year ago') order by rate desc, lower(lname), lower(coalesce((aname || ' ') || fmname, fmname, aname)), gen, genlab, cname_web.areaid;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1308.35..1308.44 rows=39 width=203) (actual time=1152.67..1152.68 rows=25 loops=1)
Sort Key: crate.rate, lower(cname_web.lname), lower(CASE WHEN (((cname_web.aname || ' '::text) || cname_web.fmname) IS NOT NULL) THEN ((cname_web.aname || ' '::text) || cname_web.fmname) WHEN (cname_web.fmname IS NOT NULL) THEN cname_web.fmname WHEN (cname_web.aname IS NOT NULL) THEN cname_web.aname ELSE NULL::text END), cname_web.gen, cname_web.genlab, cname_web.areaid
-> Merge Join (cost=1270.71..1307.31 rows=39 width=203) (actual time=1120.23..1152.25 rows=25 loops=1)
Merge Cond: ("outer".areaid = "inner".areaid)
-> Sort (cost=681.95..699.97 rows=7208 width=63) (actual time=1079.55..1083.66 rows=7147 loops=1)
Sort Key: cname_web.areaid
-> Subquery Scan cname_web (cost=0.00..220.08 rows=7208 width=63) (actual time=0.40..843.48 rows=7208 loops=1)
-> Seq Scan on cname (cost=0.00..220.08 rows=7208 width=63) (actual time=0.40..818.24 rows=7208 loops=1)
InitPlan
-> Seq Scan on priv (cost=0.00..1.09 rows=1 width=4) (actual time=0.04..0.05 rows=1 loops=1)
Filter: (pname = 'web'::text)
-> Seq Scan on priv (cost=0.00..1.09 rows=1 width=4) (actual time=0.01..0.02 rows=1 loops=1)
Filter: (pname = 'web'::text)
-> Seq Scan on priv (cost=0.00..1.09 rows=1 width=4) (actual time=0.01..0.02 rows=1 loops=1)
Filter: (pname = 'web'::text)
-> Seq Scan on priv (cost=0.00..1.09 rows=1 width=4) (actual time=0.01..0.02 rows=1 loops=1)
Filter: (pname = 'web'::text)
-> Seq Scan on priv (cost=0.00..1.09 rows=1 width=4) (actual time=0.01..0.02 rows=1 loops=1)
Filter: (pname = 'web'::text)
-> Seq Scan on priv (cost=0.00..1.09 rows=1 width=4) (actual time=0.01..0.02 rows=1 loops=1)
Filter: (pname = 'web'::text)
SubPlan
-> Seq Scan on priv (cost=0.00..1.09 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=7208)
Filter: (pname = $1)
-> Seq Scan on priv (cost=0.00..1.09 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=7208)
Filter: (pname = $1)
-> Seq Scan on priv (cost=0.00..1.09 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=7208)
Filter: (pname = $1)
-> Seq Scan on priv (cost=0.00..1.09 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=7208)
Filter: (pname = $1)
-> Seq Scan on priv (cost=0.00..1.09 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=7208)
Filter: (pname = $1)
-> Seq Scan on priv (cost=0.00..1.09 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=7208)
Filter: (pname = $1)
-> Sort (cost=588.76..588.80 rows=16 width=39) (actual time=39.95..39.96 rows=25 loops=1)
Sort Key: crate.areaid
-> Seq Scan on crate (cost=0.00..588.45 rows=16 width=39) (actual time=3.14..39.58 rows=25 loops=1)
Filter: ((gameid = '776'::text) AND (frq > 0) AND (touched >= '2000-12-24 12:40:01'::timestamp without time zone))
Total runtime: 1155.29 msec
(39 rows)