0

Postgres server running very slow. DB has two tables and when I run explain analyze for below query, taking more than 10min and query running forever.

Tables DDL, db<>fiddle.

User query:

explain analyze select COUNT(*) as "DII_1"
  from ( select table0."individual_entity_proxy_id" as "INDIVIDUAL_ENTITY_PROXY_ID"
                                from test1 table0
                                inner join test2 table1
                                on table0."individual_entity_proxy_id" = table1."individual_entity_proxy_id"
                                where ((table0."shared_paddr_with_customer_ind" = 'N')
                                                   and (table0."profane_wrd_ind" = 'N')
                                                   and (table0."tmo_ofnsv_name_ind" = 'N')
                                                   and ((table0."last_contacted_dm_datetime" is null)
                                                   or (table0."last_contacted_dm_datetime" <  TIMESTAMP '2020-03-15  0:00:00.000000'))
                                                   and (table0."has_individual_address" = 'Y')
                                                   and (table0."has_last_name" = 'Y')
                                                   and (table0."has_first_name" = 'Y')
                                                   and (table0."direct_mail_preference" is null))
                                                   and ((table1."tax_bnkrpt_dcsd_ind" = 'N')
                                                  and (table1."cstmr_prspct_ind" = 'Prospect')
                                                  and (table1."govt_prison_ind" = 'N')) ) TXT_1;

Explain Analyze:

"Finalize Aggregate  (cost=3440557.89..3440557.90 rows=1 width=8) (actual time=119522.092..119522.092 rows=1 loops=1)"
"  Buffers: shared hit=5369032, temp read=949785 written=937509"
"  ->  Gather  (cost=3440557.16..3440557.87 rows=7 width=8) (actual time=119001.846..119559.076 rows=6 loops=1)"
"        Workers Planned: 7"
"        Workers Launched: 5"
"        Buffers: shared hit=5369032, temp read=949785 written=937509"
"        ->  Partial Aggregate  (cost=3439557.16..3439557.17 rows=1 width=8) (actual time=118997.002..118997.003 rows=1 loops=6)"
"              Buffers: shared hit=5369032, temp read=949785 written=937509"
"              ->  Hash Join  (cost=2093187.27..3428070.55 rows=4594644 width=0) (actual time=87536.941..118613.469 rows=5394697 loops=6)"
"                    Hash Cond: (table0.individual_entity_proxy_id = table1.individual_entity_proxy_id)"
"                    Buffers: shared hit=5369032, temp read=949785 written=937509"
"                    ->  Parallel Seq Scan on test1 table0  (cost=0.00..1130249.76 rows=4604772 width=8) (actual time=0.022..3737.959 rows=5394697 loops=6)"
"                          Filter: ((direct_mail_preference IS NULL) AND ((last_contacted_dm_datetime IS NULL) OR (last_contacted_dm_datetime < '2020-03-15 00:00:00'::timestamp without time zone)) AND (shared_paddr_with_customer_ind = 'N'::bpchar) AND (profane_wrd_ind = 'N'::bpchar) AND (tmo_ofnsv_name_ind = 'N'::bpchar) AND (has_individual_address = 'Y'::bpchar) AND (has_last_name = 'Y'::bpchar) AND (has_first_name = 'Y'::bpchar))"
"                          Rows Removed by Filter: 1284863"
"                          Buffers: shared hit=972803"
"                    ->  Hash  (cost=1435596.42..1435596.42 rows=40081668 width=8) (actual time=83938.784..83938.784 rows=40077727 loops=6)"
"                          Buckets: 131072  Batches: 1024  Memory Usage: 2553kB"
"                          Buffers: shared hit=4395726, temp written=818046"
"                          ->  Seq Scan on test2 table1  (cost=0.00..1435596.42 rows=40081668 width=8) (actual time=0.789..14573.245 rows=40077727 loops=6)"
"                                Filter: ((tax_bnkrpt_dcsd_ind = 'N'::bpchar) AND (govt_prison_ind = 'N'::bpchar) AND ((cstmr_prspct_ind)::text = 'Prospect'::text))"
"                                Rows Removed by Filter: 94756"
"                                Buffers: shared hit=4395726"
"Planning time: 3.115 ms"
"Execution time: 119559.941 ms"

Server config:

PostgreSQL v10.11
RAM: 380GB
vCore: 32
Shared_buffers: 65GB
work_mem:104857kB
maintenance_work_mem:256MB
effective_cache_size: 160GB

Any suggestions for optimization?

1 Answer 1

0

try:

explain analyze 
with testdata as (

    select table0."individual_entity_proxy_id" as "INDIVIDUAL_ENTITY_PROXY_ID"
    from test1 table0
    where ((table0."shared_paddr_with_customer_ind" = 'N')
        and (table0."profane_wrd_ind" = 'N')
        and (table0."tmo_ofnsv_name_ind" = 'N')
        and ((table0."last_contacted_dm_datetime" is null) or (table0."last_contacted_dm_datetime" <  TIMESTAMP '2020-03-15  0:00:00.000000'))
        and (table0."has_individual_address" = 'Y')
        and (table0."has_last_name" = 'Y')
        and (table0."has_first_name" = 'Y')
        and (table0."direct_mail_preference" is null))
    
    intersect
    
    select table1."individual_entity_proxy_id"
    from test2 table1
    where ((table1."tax_bnkrpt_dcsd_ind" = 'N')
        and (table1."cstmr_prspct_ind" = 'Prospect')
        and (table1."govt_prison_ind" = 'N')) 
)

select COUNT(*) as "DII_1"
from  testdata

if this not help - probably you need to create indexes, maybe change fields char to bool ('Y' -> true, 'N' -> false)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.