1

I try to run the following query to identify duplicate IDs in a partitioned table (2432 partitions, all more or less 221000 rows). I created the partitions based on a geometry, not Range or List. The global_id is indexed in the partitions and the parent table.

SELECT global_id,
       count(global_id)
FROM poly.centers_res17
GROUP BY global_id HAVING count(global_id) > 1;

The query plan, looks good to me (notice the Workers Planned says 3 in this plan, but I guess that is changing as workers become available)

Finalize GroupAggregate  (cost=19423295.80..19423303.80 rows=200 width=16)
  Group Key: centers_res17.global_id
  Filter: (count(centers_res17.global_id) > 1)
  ->  Sort  (cost=19423295.80..19423297.30 rows=600 width=16)
        Sort Key: centers_res17.global_id
        ->  Gather  (cost=19423206.11..19423268.11 rows=600 width=16)
              Workers Planned: 3
              ->  Partial HashAggregate  (cost=19422206.11..19422208.11 rows=200 width=16)
                    Group Key: centers_res17.global_id
                    ->  Append  (cost=0.00..16652512.60 rows=553938702 width=8)
                          ->  Parallel Seq Scan on centers_res17  (cost=0.00..0.00 rows=1 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0001  (cost=0.00..5543.82 rows=184782 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0002  (cost=0.00..5543.82 rows=184782 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0003  (cost=0.00..6670.45 rows=222345 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0004  (cost=0.00..6670.45 rows=222345 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0005  (cost=0.00..6664.43 rows=222143 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0006  (cost=0.00..6656.98 rows=221898 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0007  (cost=0.00..6655.31 rows=221831 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0008  (cost=0.00..6661.42 rows=222042 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0009  (cost=0.00..6667.44 rows=222244 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0010  (cost=0.00..6667.44 rows=222244 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0011  (cost=0.00..6662.54 rows=222054 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0012  (cost=0.00..6658.40 rows=221940 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0013  (cost=0.00..6658.40 rows=221940 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0014  (cost=0.00..6664.43 rows=222143 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0015  (cost=0.00..6662.64 rows=222064 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0016  (cost=0.00..6661.42 rows=222042 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0017  (cost=0.00..6655.39 rows=221839 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0018  (cost=0.00..6655.39 rows=221839 width=8)
                          ->  Parallel Seq Scan on centers_res17_p0019  (cost=0.00..6656.94 rows=221894 width=8) 
... and so on 

This results in an error

ERROR: out of memory

DETAIL: Failed on request of size 2048.

CONTEXT: parallel worker

I am running PostgreSQL 9.6 on Win10 with 32GB RAM and a CPU with 8 Logical processors.

My postgresql.conf has these settings.

  • shared_buffers 8GB
  • effective_cache_size 16GB
  • work_mem 8MB (I tried it with 128MB, 1GB, and 2GB)
  • max_locks_per_transaction 256
  • max_worker_processes 8
  • max_parallel_workers_per_gather 6

When running this query my CPU and RAM go immediately to almost 100% and disk activity is at 100% as well. This goes on for ~2 hours, only then the "Out of Memory" error comes. My guess is that this is the Partial HashAggregate or Gather step from the query plan where more Memory is requested.

Questions

  • What other pg parameters should I change/consider?
  • Is there a better way to deal with duplicate identification in partitioned tables?
  • "Failed on request of size 2048" 2GB of RAM?

Edit 1

I have decreaed the memory settings even more and created a temp_tablespaces on another empty disk

  • shared_buffers 1GB
  • effective_cache_size 6GB
  • work_mem 512kB
  • max_worker_processes 6
  • max_parallel_workers_per_gather 4

With these settings I hoped to encourage postgres to wirte to disk, but it seems my temp_tablespaces is not used at all. I set the GRANT ALL ON TABLESPACE fast_ssd TO "Username";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.