Re: Premature view materialization in 8.2? - Mailing list pgsql-performance

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