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:25:56
Message-ID: 20021224212556.GA16170@wolff.to (view raw or flat)
Thread:
Lists: pgsql-performance
By disabling merge joins and using the updated view, I got the query down
to about 25% of its original runtime.
Note the query estimate is off by a factor of more than 10.
                                                                                                                                                             QUERY PLAN                                                                                                                                                             
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3271.35..3271.39 rows=15 width=113) (actual time=232.25..232.27 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)
   ->  Hash Join  (cost=355.71..3271.05 rows=15 width=113) (actual time=106.82..231.97 rows=25 loops=1)
         Hash Cond: ("outer".areaid = "inner".areaid)
         ->  Hash Join  (cost=219.25..431.41 rows=7208 width=74) (actual time=103.86..222.00 rows=7208 loops=1)
               Hash Cond: ("outer".areaid = "inner".areaid)
               ->  Seq Scan on cname a  (cost=0.00..146.08 rows=7208 width=11) (actual time=0.01..16.23 rows=7208 loops=1)
               ->  Hash  (cost=213.25..213.25 rows=2403 width=63) (actual time=103.70..103.70 rows=0 loops=1)
                     ->  Hash Join  (cost=1.09..213.25 rows=2403 width=63) (actual time=0.35..88.82 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..29.73 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)
         ->  Hash  (cost=136.42..136.42 rows=15 width=39) (actual time=0.72..0.72 rows=0 loops=1)
               ->  Index Scan using crate_game on crate  (cost=0.00..136.42 rows=15 width=39) (actual time=0.10..0.66 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: 232.83 msec
(22 rows)


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2002-12-26 19:42:39
Subject: Re: View performance
Previous:From: Bruno Wolff IIIDate: 2002-12-24 21:06:37
Subject: Re: View performance

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