pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Claus Guttesen <kometen@gmail.com>
To: Rolf Østvik <rolfostvik@yahoo.no>
Cc: pgsql-performance@postgresql.org
Subject: Re: Worse perfomance on 8.2.0 than on 7.4.14
Date: Sun, 31 Dec 2006 13:16:43 +0100
Message-ID: <b41c75520612310416w7639d7a5kdb7e6a59180a5905@mail.gmail.com> (raw)
In-Reply-To: <200612311233.10201.rolfostvik@yahoo.no>
References: <200612311233.10201.rolfostvik@yahoo.no>

> 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.

regards
Claus



view thread (17+ messages)  latest in thread

Message-ID: <b41c75520612310416w7639d7a5kdb7e6a59180a5905@mail.gmail.com>
Permalink:  ../b41c75520612310416w7639d7a5kdb7e6a59180a5905@mail.gmail.com/
Also on:    postgresql.org/message-id/b41c75520612310416w7639d7a5kdb7e6a59180a5905@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: kometen@gmail.com, rolfostvik@yahoo.no
  Subject: Re: Worse perfomance on 8.2.0 than on 7.4.14
  In-Reply-To: <b41c75520612310416w7639d7a5kdb7e6a59180a5905@mail.gmail.com>
  References: <200612311233.10201.rolfostvik@yahoo.no> <b41c75520612310416w7639d7a5kdb7e6a59180a5905@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox