2017-04-05 9:28 GMT+02:00 Daniel Westermann < daniel.westerm...@dbi-services.com>:
> >>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) > There is a materialize op more do you have a index on ids.id? Pavel
