View performance - Mailing list pgsql-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)
ISNOT NULL) THEN ((cname_web.aname || ' '::text) || cname_web.fmname) WHEN (cname_web.fmname IS NOT NULL) THEN
cname_web.fmnameWHEN (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
withouttime zone)) 
 Total runtime: 1155.29 msec
(39 rows)