explain.depesz.com

PostgreSQL's explain analyze made readable

Result: StT8 : Optimization for: plan #gn4o

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 9,631.459 3,766,207.622 ↑ 1.4 5,894,009 1

Nested Loop Left Join (cost=63,172,030.22..84,719,370.51 rows=8,437,832 width=448) (actual time=2,989,845.751..3,766,207.622 rows=5,894,009 loops=1)

  • Filter: (((policy_osago.is_tech_cor = 0) AND (policy_osago.recall_policy_unq_id = 0) AND
  • (policy_osago.ins_company_id <> 199)) OR ((rls.is_tech_cor = 0) AND (rls.recall_loss_rec_id = 0)) OR ((rls.is_tech_cor = 0) AND (rls_only.recall_loss_rec_id = 0)))
  • Rows Removed by Filter: 112245532
2. 163,591.246 3,165,878.458 ↓ 13.0 118,139,541 1

Hash Right Join (cost=63,172,030.11..64,973,056.31 rows=9,116,710 width=476) (actual time=2,989,838.275..3,165,878.458 rows=118,139,541 loops=1)

  • Hash Cond: (rls_only.glt_subj_osago_rec_id = rso.subj_osago_rec_id)
3. 12,498.762 12,498.762 ↑ 1.0 19,392,939 1

Seq Scan on loss rls_only (cost=0.00..994,460.58 rows=20,231,666 width=16) (actual time=0.052..12,498.762 rows=19,392,939 loops=1)

  • Filter: ((is_tech_cor = 0) AND (recall_loss_rec_id = 0))
  • Rows Removed by Filter: 16237341
4. 140,238.955 2,989,788.450 ↓ 12.5 114,099,538 1

Hash (cost=62,588,132.62..62,588,132.62 rows=9,116,710 width=468) (actual time=2,989,788.450..2,989,788.450 rows=114,099,538 loops=1)

  • Buckets: 1048576 (originally 1048576) Batches: 64 (originally 16) Memory Usage: 516097kB
  • -> Hash Right Join (cost=61075785.84..62588132.62 rows=9116710 width=468)(actual time=2849802.241..2926907.372 rows=114099538 loops=1)
  • Hash Cond: (rls.glt_policy_subj_info_id = policy_subj_info.policy_subj_info_id)
5. 5,470.078 6,688.008 ↑ 1.1 5,837,799 1

Bitmap Heap Scan on loss rls (cost=44,433.75..921,765.98 rows=6,351,057 width=20) (actual time=1,376.633..6,688.008 rows=5,837,799 loops=1)

  • Recheck Cond: ((glt_subj_osago_rec_id IS NULL) AND (is_tech_cor= 0) AND (recall_loss_rec_id = 0))
  • Heap Blocks: exact=636756
6. 1,217.930 1,217.930 ↑ 1.1 5,837,799 1

Bitmap Index Scan on loss_idx_cond (cost=0.00..44,116.20 rows=6,351,057 width=0) (actual time=1,217.929..1,217.930 rows=5,837,799 loops=1)

  • Index Cond: (glt_subj_osago_rec_id IS NULL)
7. 59,931.304 2,842,861.487 ↓ 12.5 114,099,538 1

Hash (cost=60,456,357.60..60,456,357.60 rows=9,116,710 width=464) (actual time=2,842,861.487..2,842,861.487 rows=114,099,538 loops=1)

  • Buckets: 1048576 (originally 1048576) Batches: 8192 (originally 16) Memory Usage: 26575189kB
8. 1,851,622.208 2,782,930.183 ↓ 12.5 114,099,538 1

Hash Right Join (cost=418,387.67..60,456,357.60 rows=9,116,710 width=464) (actual time=160,356.778..2,782,930.183 rows=114,099,538 loops=1)

  • Hash Cond: (policy_subj_info.subj_osago_rec_id = rso.subj_osago_rec_id)
9. 772,212.518 772,212.518 ↑ 1.0 2,414,460,997 1

Seq Scan on policy_subj_info (cost=0.00..58,199,487.64 rows=2,414,842,880 width=24) (actual time=0.022..772,212.518 rows=2,414,460,997 loops=1)

10. 57,312.397 159,095.457 ↓ 102.7 107,966,181 1

Hash (cost=414,708.79..414,708.79 rows=1,051,108 width=448) (actual time=159,095.455..159,095.457 rows=107,966,181 loops=1)

  • Buckets: 2097152 (originally 2097152) Batches: 64 (originally 1) Memory Usage: 507905kB
11. 16,385.311 101,783.060 ↓ 102.7 107,966,181 1

Nested Loop (cost=0.11..414,708.79 rows=1,051,108 width=448) (actual time=0.083..101,783.060 rows=107,966,181 loops=1)

12. 10.069 10.069 ↓ 2.3 41,370 1

Seq Scan on tmp_subject_osago_doc_tmp1 so (cost=0.00..964.60 rows=18,200 width=376) (actual time=0.021..10.069 rows=41,370 loops=1)

13. 85,387.680 85,387.680 ↓ 45.0 2,610 41,370

Index Scan using idx_subject_osago_subj_osago_doc_rec_id on subject_osago rso (cost=0.11..22.56 rows=58 width=80) (actual time=0.009..2.064 rows=2,610 loops=41,370)

  • Index Cond: (subj_osago_doc_rec_id = so.doc_rec_id)
14. 590,697.705 590,697.705 ↓ 0.0 0 118,139,541

Index Scan using pk_policy_osago on policy_osago (cost=0.12..2.16 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=118,139,541)

  • Index Cond: (policy_rec_id = policy_subj_info.policy_rec_id)
  • Filter: ((ins_company_id <> 199) AND (is_tech_cor = 0) AND (recall_policy_unq_id = 0))
  • Rows Removed by Filter: 0
Planning time : 13.837 ms
Execution time : 3,782,149.657 ms