pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: Jonathan Ellis <jonathan@utahpython.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Premature view materialization in 8.2?
Date: Thu, 5 Apr 2007 23:51:29 -0600
Message-ID: <e06563880704052251j68c86dafx5ba5a8f10fb28268@mail.gmail.com> (raw )
In-Reply-To: <28349.1175838196@sss.pgh.pa.us >
References: <e06563880704051146r357cb15by7e498c6d796b093a@mail.gmail.com >
<28349.1175838196@sss.pgh.pa.us >
On 4/5/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Jonathan Ellis" <jonathan@utahpython.org> writes:
> > I have a query hitting a view that takes 0.15s on 8.1 but 6s on 8.2.3:
> > ...
> > Is this a regression, or a "feature" of 8.2?
>
> Hard to say without EXPLAIN ANALYZE output to compare.
To my eye they are identical other than the speed but perhaps I am
missing something.
8.2:
Hash Join (cost=91.94..560.71 rows=259 width=4) (actual
time=22.120..6388.754 rows=958 loops=1)
Hash Cond: (cm.clan_id = c.id)
-> Hash Join (cost=75.34..536.90 rows=336 width=36) (actual
time=19.542..6375.827 rows=1298 loops=1)
Hash Cond: (p.user_id = cm.user_id)
-> Hash Join (cost=36.32..487.94 rows=1303 width=24)
(actual time=9.019..95.583 rows=1299 loops=1)
Hash Cond: (p.id = cp.party_id)
-> Seq Scan on parties p (cost=0.00..397.52
rows=10952 width=20) (actual time=0.013..40.558 rows=10952 loops=1)
-> Hash (cost=20.03..20.03 rows=1303 width=4) (actual
time=8.545..8.545 rows=1299 loops=1)
-> Seq Scan on clan_participants cp
(cost=0.00..20.03 rows=1303 width=4) (actual time=0.013..4.063
rows=1299 loops=1)
-> Hash (cost=22.90..22.90 rows=1290 width=16) (actual
time=8.748..8.748 rows=1294 loops=1)
-> Seq Scan on clan_members cm (cost=0.00..22.90
rows=1290 width=16) (actual time=0.013..4.307 rows=1294 loops=1)
-> Hash (cost=11.99..11.99 rows=369 width=4) (actual
time=2.550..2.550 rows=368 loops=1)
-> Seq Scan on clans c (cost=0.00..11.99 rows=369 width=4)
(actual time=0.025..1.341 rows=368 loops=1)
Filter: (("type")::text = 'standard'::text)
Total runtime: 6391.999 ms
8.1:
Hash Join (cost=62.37..681.10 rows=254 width=4) (actual
time=25.316..138.613 rows=967 loops=1)
Hash Cond: ("outer".clan_id = "inner".id)
-> Hash Join (cost=49.46..664.00 rows=331 width=8) (actual
time=21.331..126.194 rows=1305 loops=1)
Hash Cond: ("outer".user_id = "inner".user_id)
-> Hash Join (cost=23.32..628.02 rows=1306 width=8) (actual
time=10.674..105.352 rows=1306 loops=1)
Hash Cond: ("outer".id = "inner".party_id)
-> Seq Scan on parties p (cost=0.00..537.09
rows=10909 width=8) (actual time=0.018..49.754 rows=10855 loops=1)
-> Hash (cost=20.06..20.06 rows=1306 width=4) (actual
time=10.334..10.334 rows=1306 loops=1)
-> Seq Scan on clan_participants cp
(cost=0.00..20.06 rows=1306 width=4) (actual time=0.020..5.172
rows=1306 loops=1)
-> Hash (cost=22.91..22.91 rows=1291 width=8) (actual
time=10.621..10.621 rows=1291 loops=1)
-> Seq Scan on clan_members cm (cost=0.00..22.91
rows=1291 width=8) (actual time=0.019..5.381 rows=1291 loops=1)
-> Hash (cost=11.99..11.99 rows=368 width=4) (actual
time=3.834..3.834 rows=368 loops=1)
-> Seq Scan on clans c (cost=0.00..11.99 rows=368 width=4)
(actual time=0.043..2.373 rows=368 loops=1)
Filter: (("type")::text = 'standard'::text)
Total runtime: 142.209 ms
view thread (11+ messages) latest in thread
Message-ID: <e06563880704052251j68c86dafx5ba5a8f10fb28268@mail.gmail.com>
Permalink: ../e06563880704052251j68c86dafx5ba5a8f10fb28268@mail.gmail.com/
Also on: postgresql.org/message-id/e06563880704052251j68c86dafx5ba5a8f10fb28268@mail.gmail.com
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 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: jonathan@utahpython.org, tgl@sss.pgh.pa.us
Subject: Re: Premature view materialization in 8.2?
In-Reply-To: <e06563880704052251j68c86dafx5ba5a8f10fb28268@mail.gmail.com>
References: <e06563880704051146r357cb15by7e498c6d796b093a@mail.gmail.com> <28349.1175838196@sss.pgh.pa.us> <e06563880704052251j68c86dafx5ba5a8f10fb28268@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