From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Daniel Westermann <daniel(dot)westermann(at)dbi-services(dot)com> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query never completes with low work_mem (at least not within one hour) |
Date: | 2017-04-05 08:06:25 |
Message-ID: | CAFj8pRB6Job_e7iXYB+zuZn962c3XgfJYNza_6rrfKn0ML3rAw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2017-04-05 9:28 GMT+02:00 Daniel Westermann <
daniel(dot)westermann(at)dbi-services(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Westermann | 2017-04-05 08:13:34 | Re: Query never completes with low work_mem (at least not within one hour) |
Previous Message | Chris Mair | 2017-04-05 08:01:52 | expensive function in select list vs limit clause |