Skip site navigation (1) Skip section navigation (2)

Re: View performance

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: View performance
Date: 2002-12-24 21:06:37
Message-ID: 20021224210637.GA16085@wolff.to (view raw or flat)
Thread:
Lists: pgsql-performance
As a followup to this I rewrote the view as:
create view cname_web as select
  a.areaid, b.lname, b.fmname, b.aname, b.gen, b.genlab, b.touched
  from cname a left join
    (select areaid, lname, fmname, aname, gen, genlab, touched, privacy
      from cname, priv
      where pname = privacy and
      pord <= (select pord from priv where pname = 'web')
    ) b
    using (areaid);

And got the query down to about half the original time as shown here:
                                                                                                                                                             QUERY PLAN                                                                                                                                                             
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=970.71..970.74 rows=15 width=113) (actual time=550.82..550.83 rows=25 loops=1)
   Sort Key: crate.rate, lower(cname.lname), lower(CASE WHEN (((cname.aname || ' '::text) || cname.fmname) IS NOT NULL) THEN ((cname.aname || ' '::text) || cname.fmname) WHEN (cname.fmname IS NOT NULL) THEN cname.fmname WHEN (cname.aname IS NOT NULL) THEN cname.aname ELSE NULL::text END), cname.gen, cname.genlab, a.areaid
   InitPlan
     ->  Seq Scan on priv  (cost=0.00..1.09 rows=1 width=4) (actual time=0.02..0.03 rows=1 loops=1)
           Filter: (pname = 'web'::text)
   ->  Merge Join  (cost=484.88..970.41 rows=15 width=113) (actual time=361.92..550.53 rows=25 loops=1)
         Merge Cond: ("outer".areaid = "inner".areaid)
         ->  Merge Join  (cost=348.16..815.45 rows=7208 width=74) (actual time=358.29..520.50 rows=7147 loops=1)
               Merge Cond: ("outer".areaid = "inner".areaid)
               ->  Index Scan using cname_pkey on cname a  (cost=0.00..407.27 rows=7208 width=11) (actual time=0.03..26.59 rows=7147 loops=1)
               ->  Sort  (cost=348.16..354.17 rows=2403 width=63) (actual time=358.20..362.38 rows=7141 loops=1)
                     Sort Key: cname.areaid
                     ->  Hash Join  (cost=1.09..213.25 rows=2403 width=63) (actual time=0.35..94.32 rows=7202 loops=1)
                           Hash Cond: ("outer".privacy = "inner".pname)
                           ->  Seq Scan on cname  (cost=0.00..146.08 rows=7208 width=55) (actual time=0.01..33.41 rows=7208 loops=1)
                           ->  Hash  (cost=1.09..1.09 rows=2 width=8) (actual time=0.07..0.07 rows=0 loops=1)
                                 ->  Seq Scan on priv  (cost=0.00..1.09 rows=2 width=8) (actual time=0.06..0.07 rows=2 loops=1)
                                       Filter: (pord <= $0)
         ->  Sort  (cost=136.72..136.76 rows=15 width=39) (actual time=0.95..0.96 rows=25 loops=1)
               Sort Key: crate.areaid
               ->  Index Scan using crate_game on crate  (cost=0.00..136.42 rows=15 width=39) (actual time=0.10..0.67 rows=25 loops=1)
                     Index Cond: (gameid = '776'::text)
                     Filter: ((frq > 0) AND (touched >= '2000-12-24 12:40:01'::timestamp without time zone))
 Total runtime: 553.17 msec
(24 rows)

On Tue, Dec 24, 2002 at 14:16:38 -0600,
  Bruno Wolff III <bruno(at)wolff(dot)to> wrote:
> 
> 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;

In response to

Responses

pgsql-performance by date

Next:From: Bruno Wolff IIIDate: 2002-12-24 21:25:56
Subject: Re: View performance
Previous:From: Bruno Wolff IIIDate: 2002-12-24 20:16:38
Subject: View performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group