Postgres Pro
Downloads
Home   >   mailing lists

Re: Query never completes with low work_mem (at least notwithin one hour) - Mailing list pgsql-general

From Daniel Westermann
Subject Re: Query never completes with low work_mem (at least notwithin one hour)
Date
Msg-id 1837861226.253099.1491377283719.JavaMail.zimbra@dbi-services.com
Whole thread Raw
In response to Re: Query never completes with low work_mem (at least notwithin one hour)  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-general
Tree view
>>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)

pgsql-general by date:

Previous
From: Marc Tempelmeier
Date:
Subject: Re: Keycloak and Postgres
Next
From: Chris Mair
Date:
Subject: expensive function in select list vs limit clause