pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Markus Wollny <Markus.Wollny@computec.de>
To: Markus Wollny <Markus.Wollny@computec.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Queries taking ages in PG 8.1, have been much faster in PG<=8.0
Date: Sun, 4 Dec 2005 14:24:37 +0100
Message-ID: <28011CD60FB1724DBA4442E38277F626114E3A@hermes.computec.de> (raw)
References: <18959924631B864DB8D5268D81D746A90CCE2DD9@hermes.computec.de>

Hi!

> -----Ursprüngliche Nachricht-----
> Von: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
> Gesendet: Donnerstag, 1. Dezember 2005 17:26
> An: Markus Wollny
> Cc: pgsql-performance@postgresql.org
> Betreff: Re: [PERFORM] Queries taking ages in PG 8.1, have 
> been much faster in PG<=8.0 
 
> It looks like "set enable_nestloop = 0" might be a workable 
> hack for the immediate need.  
>
> Once you're not under deadline, 
> I'd like to investigate more closely to find out why 8.1 does 
> worse than 8.0 here.


I've just set up a PostgreSQL 8.0.3 installation ...

select version();
                                          version
--------------------------------------------------------------------------------------------
 PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13)
(1 row)

...and restored a dump there; here's the explain analyze of the query for 8.0.3:

                                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=5193.63..5193.63 rows=3 width=16) (actual time=7365.107..7365.110 rows=3 loops=1)
   Sort Key: source."position"
   ->  HashAggregate  (cost=5193.59..5193.60 rows=3 width=16) (actual time=7365.034..7365.041 rows=3 loops=1)
         ->  Nested Loop  (cost=0.00..5193.57 rows=3 width=16) (actual time=3190.642..7300.820 rows=11086 loops=1)
               ->  Nested Loop  (cost=0.00..3602.44 rows=4 width=20) (actual time=3169.968..5875.153 rows=11087 loops=1)
                     ->  Nested Loop  (cost=0.00..1077.95 rows=750 width=16) (actual time=36.599..2778.129 rows=158288 loops=1)
                           ->  Seq Scan on handy_java source  (cost=0.00..1.03 rows=3 width=14) (actual time=6.503..6.514 rows=3 loops=1)
                           ->  Index Scan using idx02_performance on answer  (cost=0.00..355.85 rows=250 width=8) (actual time=10.071..732.746 rows=52763 loops=3)
                                 Index Cond: ((answer.question_id = 16) AND (answer.value = "outer".id))
                     ->  Index Scan using pk_participant on participant  (cost=0.00..3.35 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=158288)
                           Index Cond: (participant.session_id = "outer".session_id)
                           Filter: ((status = 1) AND (date_trunc('month'::text, created) = date_trunc('month'::text, (now() - '2 mons'::interval))))
               ->  Index Scan using idx_answer_session_id on answer  (cost=0.00..397.77 rows=1 width=4) (actual time=0.080..0.122 rows=1 loops=11087)
                     Index Cond: ("outer".session_id = answer.session_id)
                     Filter: ((question_id = 6) AND (value = 1))
 Total runtime: 7365.461 ms
(16 rows)

Does this tell you anything useful? It's not on the same machine, mind you, but configuration for PostgreSQL is absolutely identical (apart from the autovacuum-lines which 8.0.3 doesn't like).

Kind regards

   Markus

view thread (13+ messages)  latest in thread

Message-ID: <28011CD60FB1724DBA4442E38277F626114E3A@hermes.computec.de>
Permalink:  ../28011CD60FB1724DBA4442E38277F626114E3A@hermes.computec.de/
Also on:    postgresql.org/message-id/28011CD60FB1724DBA4442E38277F626114E3A@hermes.computec.de

 · 

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: Markus.Wollny@computec.de, tgl@sss.pgh.pa.us
  Subject: Re: Queries taking ages in PG 8.1, have been much faster in PG<=8.0
  In-Reply-To: <28011CD60FB1724DBA4442E38277F626114E3A@hermes.computec.de>
  References: <18959924631B864DB8D5268D81D746A90CCE2DD9@hermes.computec.de> <28011CD60FB1724DBA4442E38277F626114E3A@hermes.computec.de>

* 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