On 09/20/2012 01:14 PM, l...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs