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

Re: Worse perfomance on 8.2.0 than on 7.4.14

From: Rolf Østvik <rolfostvik(at)yahoo(dot)no>
To: pgsql-performance(at)postgresql(dot)org
Cc: Claus Guttesen <kometen(at)gmail(dot)com>
Subject: Re: Worse perfomance on 8.2.0 than on 7.4.14
Date: 2006-12-31 12:54:09
Message-ID: 20061231125410.34071.qmail@web26302.mail.ukl.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-performance
--- Claus Guttesen <kometen(at)gmail(dot)com> skrev:

> > I have a simple query which uses 32ms on 7.4.14
> and 1015ms on 8.2.0.
> > I guess 7.4.14 creates a better execution plan
> than 8.2.0 for this query but
> > i don't know how to get it to select a better one.
> > Explain analyse output will be found near the end
> of the e-mail.
> >
> > Explain analyze is run several times to get a
> stable result
> > so i guess the numbers presented is with as much
> as possible
> > data in memory buffers.
> >
> > Query: "select * from view_subset;" run against
> 7.4.14 server.
> > QUERY PLAN
> >
>
------------------------------------------------------------------------
> >  Nested Loop  (cost=0.00..1400.86 rows=17 width=8)
> (actual time=0.161..26.287 rows=68 loops=1)
> >    ->  Index Scan using
> uut_result_subset_start_date_time_idx on
> uut_result_subset ur  (cost=0.00..63.28 rows=18
> width=4) (actual time=0.052..0.195 rows=68 loops=1)
> >          Index Cond: (start_date_time >
> '2006-12-11 00:00:00'::timestamp without time zone)
> >    ->  Index Scan using step_result_uut_result_idx
> on step_result_subset sr  (cost=0.00..74.28 rows=2
> width=8) (actual time=0.149..0.379 rows=1 loops=68)
> >          Index Cond: ("outer".id = sr.uut_result)
> >          Filter: (step_parent = 0)
> >  Total runtime: 26.379 ms
> >
> > Query: "select * from view_subset;" run against
> 8.4.0 server.
> >
> > QUERY PLAN
> >
>
----------------------------------------------------------------------
> >  Hash Join  (cost=339.61..77103.61 rows=96
> width=8) (actual time=5.249..1010.669 rows=68
> loops=1)
> >    Hash Cond: (sr.uut_result = ur.id)
> >    ->  Index Scan using
> step_result_subset_parent_key on step_result_subset
> sr  (cost=0.00..76047.23 rows=143163 width=8)
> (actual time=0.082..905.326 rows=176449 loops=1)
> >          Index Cond: (step_parent = 0)
> >    ->  Hash  (cost=339.31..339.31 rows=118
> width=4) (actual time=0.149..0.149 rows=68 loops=1)
> >          ->  Bitmap Heap Scan on uut_result_subset
> ur  (cost=4.90..339.31 rows=118 width=4) (actual
> time=0.060..0.099 rows=68 loops=1)
> >                Recheck Cond: (start_date_time >
> '2006-12-11 00:00:00'::timestamp without time zone)
> >                ->  Bitmap Index Scan on
> uut_result_subset_start_date_time_idx 
> (cost=0.00..4.90 rows=118 width=0) (actual
> time=0.050..0.050 rows=68 loops=1)
> >                      Index Cond: (start_date_time
> > '2006-12-11 00:00:00'::timestamp without time
> zone)
> >  Total runtime: 1010.775 ms
> 
> Did you lower random_page_cost in 8.2 (which
> defaults to 4.0)? If not try 2.

Thanks for the suggestion, but it was no change of
result.
 
> regards
> Claus


__________________________________________________
Bruker du Yahoo!?
Lei av spam?  Yahoo! Mail har den beste spambeskyttelsen 
http://no.mail.yahoo.com 

In response to

pgsql-performance by date

Next:From: Dennis BjorklundDate: 2006-12-31 15:27:12
Subject: Re: Worse perfomance on 8.2.0 than on 7.4.14
Previous:From: Claus GuttesenDate: 2006-12-31 12:16:43
Subject: Re: Worse perfomance on 8.2.0 than on 7.4.14

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