>>what is result of EXPLAIN statement for slow and fast cases? >> >>regards >> >>Pavel
For work_mem=32MB explain (analyze,verbose,buffers) select count(user_id) from users where user_id not in ( select id from ids); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=83175.01..83175.02 rows=1 width=8) (actual time=7945.592..7945.593 rows=1 loops=1) Output: count(users.user_id) Buffers: shared read=29425 -> Seq Scan on public.users (cost=16925.01..79425.01 rows=1500000 width=4) (actual time=1928.665..5888.645 rows=2499999 loops=1) Output: users.user_id, users.username Filter: (NOT (hashed SubPlan 1)) Rows Removed by Filter: 500001 Buffers: shared read=29425 SubPlan 1 -> Seq Scan on public.ids (cost=0.00..14425.01 rows=1000001 width=4) (actual time=11.111..868.382 rows=1000001 loops=1) Output: ids.id Buffers: shared read=4425 Planning time: 187.396 ms Execution time: 7948.108 ms (14 rows) Time: 8244.493 ms For work_mem='16MB' it does not complete with analyze in on hour. For explain only: explain (verbose) select count(user_id) from users where user_id not in ( select id from ids); QUERY PLAN ---------------------------------------------------------------------------------------- Aggregate (cost=38748092500.00..38748092500.01 rows=1 width=8) Output: count(users.user_id) -> Seq Scan on public.users (cost=0.00..38748088750.00 rows=1500000 width=4) Output: users.user_id, users.username Filter: (NOT (SubPlan 1)) SubPlan 1 -> Materialize (cost=0.00..23332.01 rows=1000001 width=4) Output: ids.id -> Seq Scan on public.ids (cost=0.00..14425.01 rows=1000001 width=4) Output: ids.id (10 rows)
