pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: 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: Thu, 1 Dec 2005 17:30:42 +0100
Message-ID: <28011CD60FB1724DBA4442E38277F6264A62C1@hermes.computec.de> (raw )
> -----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.
Whow - that works miracles :)
"Sort (cost=81813.13..81813.14 rows=3 width=16) (actual time=7526.745..7526.751 rows=3 loops=1)"
" Sort Key: source."position""
" -> HashAggregate (cost=81813.07..81813.11 rows=3 width=16) (actual time=7526.590..7526.601 rows=3 loops=1)"
" -> Merge Join (cost=81811.40..81813.03 rows=5 width=16) (actual time=7423.289..7479.175 rows=9806 loops=1)"
" Merge Cond: ("outer".id = "inner".value)"
" -> Sort (cost=1.05..1.06 rows=3 width=14) (actual time=0.085..0.091 rows=3 loops=1)"
" Sort Key: source.id"
" -> Seq Scan on handy_java source (cost=0.00..1.03 rows=3 width=14) (actual time=0.039..0.049 rows=3 loops=1)"
" -> Sort (cost=81810.35..81811.81 rows=583 width=8) (actual time=7423.179..7440.062 rows=9806 loops=1)"
" Sort Key: mafo.answer.value"
" -> Hash Join (cost=27164.31..81783.57 rows=583 width=8) (actual time=6757.521..7360.822 rows=9806 loops=1)"
" Hash Cond: ("outer".session_id = "inner".session_id)"
" -> Bitmap Heap Scan on answer (cost=506.17..54677.92 rows=88334 width=8) (actual time=379.245..2660.344 rows=162809 loops=1)"
" Recheck Cond: (question_id = 16)"
" -> Bitmap Index Scan on idx_answer_question_id (cost=0.00..506.17 rows=88334 width=0) (actual time=274.632..274.632 rows=162814 loops=1)"
" Index Cond: (question_id = 16)"
" -> Hash (cost=26655.21..26655.21 rows=1175 width=8) (actual time=3831.362..3831.362 rows=9806 loops=1)"
" -> Hash Join (cost=4829.33..26655.21 rows=1175 width=8) (actual time=542.227..3800.985 rows=9806 loops=1)"
" Hash Cond: ("outer".session_id = "inner".session_id)"
" -> Bitmap Heap Scan on answer (cost=182.84..21429.34 rows=20641 width=4) (actual time=292.067..2750.376 rows=165762 loops=1)"
" Recheck Cond: ((question_id = 6) AND (value = 1))"
" -> Bitmap Index Scan on idx02_performance (cost=0.00..182.84 rows=20641 width=0) (actual time=167.306..167.306 rows=165769 loops=1)"
" Index Cond: ((question_id = 6) AND (value = 1))"
" -> Hash (cost=4621.13..4621.13 rows=10141 width=4) (actual time=182.842..182.842 rows=11134 loops=1)"
" -> Index Scan using idx01_perf_0005 on participant (cost=0.01..4621.13 rows=10141 width=4) (actual time=0.632..136.126 rows=11134 loops=1)"
" Index Cond: (date_trunc('month'::text, created) = date_trunc('month'::text, (now() - '1 mon'::interval)))"
" Filter: (status = 1)"
"Total runtime: 7535.398 ms"
> 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.
Please tell me what I can do to help in clearing up this issue, I'd be very happy to help! Heck, I am happy anyway that there's such a quick fix, even if it's not a beautiful one :)
Kind regards
Markus
view thread (13+ messages) latest in thread
Message-ID: <28011CD60FB1724DBA4442E38277F6264A62C1@hermes.computec.de>
Permalink: ../28011CD60FB1724DBA4442E38277F6264A62C1@hermes.computec.de/
Also on: postgresql.org/message-id/28011CD60FB1724DBA4442E38277F6264A62C1@hermes.computec.de
copy link · copy postgr.es
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 using the --to and --cc options:
reply via email
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: <28011CD60FB1724DBA4442E38277F6264A62C1@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