On 09/20/2012 01:14 PM, l1t@tom.com wrote:
> plan of http://archives.postgresql.org/pgsql-bugs/2012-09/msg00222.php
> test=# explain select max(a.info)from sli_test a where a.id not
> in(select b.id from sli_test2 b where b.id<50000);
> QUERY PLAN
> ---------------------------------------------------------------------------------------
> Aggregate (cost=9243466274.00..9243466274.01 rows=1 width=12)
> -> Seq Scan on sli_test a (cost=0.00..9243465024.00 rows=500000
> width=12)
> Filter: (NOT (SubPlan 1))
> SubPlan 1
> -> Materialize (cost=0.00..18359.60 rows=50919 width=4)
> -> Seq Scan on sli_test2 b (cost=0.00..17906.00
> rows=50919 width=4)
> Filter: (id < 50000)
That takes about 500ms here, on 9.1. What did you expect? What's the
EXPLAIN ANALYZE if your results differ?
You've failed to show your NOT EXISTS query and plan for comparison, but
I'm guessing:
regress=# explain analyze select max(a.info)from sli_test a where not
exists
(select 1 from sli_test2 b where a.id=b.id and b.id<50000);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=50256.22..50256.23 rows=1 width=12) (actual
time=671.993..671.993 rows=1 loops=1)
-> Hash Anti Join (cost=18510.11..47877.04 rows=951671 width=12)
(actual time=103.048..459.508 rows=950001 loops=1)
Hash Cond: (a.id = b.id)
-> Seq Scan on sli_test a (cost=0.00..16274.00 rows=1000000
width=16) (actual time=0.016..71.871 rows=1000000 loops=1)
-> Hash (cost=17906.00..17906.00 rows=48329 width=4) (actual
time=86.948..86.948 rows=49999 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 1758kB
-> Seq Scan on sli_test2 b (cost=0.00..17906.00
rows=48329 width=4) (actual time=0.018..76.489 rows=49999 loops=1)
Filter: (id < 50000)
Total runtime: 672.379 ms
(9 rows)
which, if so performs signficiantly worse.
I'm struggling to understand what exactly your complaint is. It's kind
of hard to work out what the intention of the query is in real-world
terms to suggest alternatives, too.
--
Craig Ringer