Performance regression 8.3.8 -> 8.4.1 with NOT EXISTS - Mailing list pgsql-performance

From Wiktor Wodecki
Subject Performance regression 8.3.8 -> 8.4.1 with NOT EXISTS
Date
Msg-id 4B02A328.2090509@Net-m.de
Whole thread Raw
Responses Re: Performance regression 8.3.8 -> 8.4.1 with NOT EXISTS  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Performance regression 8.3.8 -> 8.4.1 with NOT EXISTS  (Grzegorz Jaśkiewicz <gryzman@gmail.com>)
List pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

we are facing a performance regression regarding certain NOT EXISTS
clauses when moving from 8.3.8 to 8.4.1. It is my understanding that the
planer treats LEFT JOINs and NOT EXISTS equally with antijoin in 8.4,
but this is causing an issue for us.

Here is the table and index definition:

antijoin=# \d a
       Table "public.a"
 Column |  Type   | Modifiers
- --------+---------+-----------
 a_id   | integer | not null
 a_oid  | integer |
 b_fk   | integer |
Indexes:
    "a_pkey" PRIMARY KEY, btree (a_id)
    "idx_a_oid" btree (a_oid)

antijoin=# \d b
       Table "public.b"
 Column |  Type   | Modifiers
- --------+---------+-----------
 b_id   | integer | not null
 c_id   | integer |
 b_fk   | integer |
 b_date | date    |
Indexes:
    "b_pkey" PRIMARY KEY, btree (b_id)
    "idx_b_b_date" btree (b_date)
    "idx_b_fk" btree (b_fk)
    "idx_c_id" btree (c_id)

antijoin=# \d c
       Table "public.c"
 Column |  Type   | Modifiers
- --------+---------+-----------
 c_id   | integer | not null
 c_bool | boolean |
Indexes:
    "c_pkey" PRIMARY KEY, btree (c_id)


The statement in question is the following:

select a_id from a
where a_oid = 5207146
and (not exists(
 select b.b_id
 from b join c on b.c_id=c.c_id
 where a.b_fk=b.b_fk
     and b.b_date>now())
);


Table statistics:
antijoin=# select count(*) from a;
  count
- ---------
 3249915
(1 row)

antijoin=# select count(*) from b;
  count
- ----------
 30616125
(1 row)

antijoin=# select count(*) from c;
 count
- -------
   261
(1 row)


The execution plan for 8.3:

QUERY PLAN
-

----------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_a_oid on a  (cost=0.00..323.38 rows=1 width=4)
(actual time=22.155..22.156 rows=1 loops=1)
   Index Cond: (a_oid = 5207146)
   Filter: (NOT (subplan))
   SubPlan
     ->  Nested Loop  (cost=0.00..314.76 rows=1 width=4) (actual
time=0.113..0.113 rows=0 loops=1)
           Join Filter: (b.c_id = c.c_id)
           ->  Index Scan using idx_b_fk on b  (cost=0.00..306.88 rows=1
width=8) (actual time=0.111..0.111 rows=0 loops=1)
                 Index Cond: ($0 = b_fk)
                 Filter: (b_date > now())
           ->  Seq Scan on c  (cost=0.00..4.61 rows=261 width=4) (never
executed)
 Total runtime: 22.197 ms
(11 rows)


The execution plan for 8.4:

QUERY PLAN

-

--------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Anti Join  (cost=3253.47..182470.42 rows=1 width=4) (actual
time=377.362..377.370 rows=1 loops=1)
   Join Filter: (a.b_fk = b.b_fk)
   ->  Index Scan using idx_a_oid on a  (cost=0.00..8.62 rows=1 width=8)
(actual time=0.019..0.025 rows=1 loops=1)
         Index Cond: (a_oid = 5207146)
   ->  Hash Join  (cost=3253.47..180297.30 rows=173159 width=4) (actual
time=137.360..336.169 rows=187509 loops=1)
         Hash Cond: (b.c_id = c.c_id)
         ->  Bitmap Heap Scan on b  (cost=3245.59..177908.50 rows=173159
width=8) (actual time=137.144..221.287 rows=187509 loops=1)
               Recheck Cond: (b_date > now())
               ->  Bitmap Index Scan on idx_b_b_date
(cost=0.00..3202.30 rows=173159 width=0) (actual time=135.152..135.152
rows=187509 loops=1)
                     Index Cond: (b_date > now())
         ->  Hash  (cost=4.61..4.61 rows=261 width=4) (actual
time=0.189..0.189 rows=261 loops=1)
               ->  Seq Scan on c  (cost=0.00..4.61 rows=261 width=4)
(actual time=0.008..0.086 rows=261 loops=1)
 Total runtime: 377.451 ms
(13 rows)

The hardware is a 4 way Quad Core2 96GB box, both databases configured
with the values:

shared_buffers=32GB
work_mem=128MB
effective_cache_size=48GB

Default statistics target is 200, all tables are freshly vacuum analyzed.
The system is x86_64 with postgres compiled from source.

As you can see the 8.4 run is 16 times slower. It was even worse before
we added the index idx_b_b_date which we didn't have initially.
Is there anything we can do about this issue? Do you need more information?

- --
Regards,

 Wiktor Wodecki

 net mobile AG, Zollhof 17, 40221 Duesseldorf, Germany
 923B DCF8 070C 9FDD 5E05  9AE3 E923 5A35 182C 9783
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAksCoygACgkQ6SNaNRgsl4PpKwCguGSDd2ehmVXM6mzzLWABEOnR
WWcAoM7PnSUyHGr0tLymFLhJuO0JtpZ5
=Oq8F
-----END PGP SIGNATURE-----

pgsql-performance by date:

Previous
From: Greg Smith
Date:
Subject: Re: Is Diskeeper Automatic Mode safe?
Next
From: "Kevin Grittner"
Date:
Subject: Re: Is Diskeeper Automatic Mode safe?