Reducing worker mem shaved about 12 minutes off the query time.. Thanks for the suggestion. I lowered it to 10MB instead of 100MB
[SNIP]
>>> [postgresql.conf] >>> max_connections = 100000 >>> max_files_per_process = 1000000 >>> shared_buffers = 24GB >>> max_locks_per_transaction = 1000 >>> effective_cache_size = 50GB >>> work_mem = 100MB >>> maintenance_work_mem = 2GB >>> log_min_duration_statement = 10000 >>> checkpoint_completion_target = 0.9 >>> wal_buffers = 32MB >>> default_statistics_target = 100 >>> listen_addresses = '*' >>> port = 5432 >>> ssl = off >>> wal_sync_method = fdatasync >>> synchronous_commit = on >>> fsync = off >>> wal_level = minimal >>> #client_min_messages = fatal >>> #log_min_messages = fatal >>> #log_min_error_statement = fatal >>> datestyle = 'iso, mdy' >>> debug_pretty_print = off >>> debug_print_parse = off >>> debug_print_plan = off >>> debug_print_rewritten = off >>> default_text_search_config = 'pg_catalog.english' >>> enable_bitmapscan = on >>> enable_hashagg = on >>> enable_hashjoin = on >>> enable_indexonlyscan = on >>> enable_indexscan = on >>> enable_material = on >>> enable_mergejoin = on >>> enable_nestloop = on >>> enable_seqscan = on >>> enable_sort = on >>> enable_tidscan = on >>> from_collapse_limit = 8 >>> geqo = on >>> geqo_threshold = 12 >>> log_checkpoints = off >>> >>> log_connections = off >>> log_disconnections = off >>> log_duration = off >>> log_executor_stats = off >>> log_hostname = off >>> log_parser_stats = off >>> log_planner_stats = off >>> log_replication_commands = off >>> log_statement_stats = off >>> log_timezone = 'UTC' >>> max_wal_size = 1GB >>> min_wal_size = 80MB >>> shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen' >>> stats_temp_directory = 'pg_stat_tmp' >>> timezone = 'US/Pacific' >>> track_activities = on >>> track_counts = on >>> track_io_timing = off >>> >>> >>> Thanks in advance. >>> >>> >>> >>> >> >>
The number of connections that you are attempting from Postgres is way too high. You should be using a connection pooler like pgbouncer, and reduce the number of connections at postgres level.