-----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-----
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
