explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QTEY

Settings
# exclusive inclusive rows x rows loops node
1. 1,600.394 3,349,237.318 ↑ 2.3 3,664,291 1

Group (cost=85,910,150.12..85,964,996.03 rows=8,437,832 width=448) (actual time=3,335,362.773..3,349,237.318 rows=3,664,291 loops=1)

  • Group Key: so.loss_id, so.is_juridical, so.is_not_resident, so.perpetrator, so.last_name, so.first_name, so.middle_name, so.doc_serial, so.doc_number, so.doc_type_id, so.doc_rec_id, rso.subj_osago_rec_id
2. 67,472.647 3,347,636.924 ↑ 1.4 5,894,009 1

Sort (cost=85,910,150.12..85,914,369.03 rows=8,437,832 width=448) (actual time=3,335,362.754..3,347,636.924 rows=5,894,009 loops=1)

  • Sort Key: so.loss_id, so.is_juridical, so.is_not_resident, so.perpetrator, so.last_name, so.first_name, so.middle_name, so.doc_serial, so.doc_number, so.doc_type_id, so.doc_rec_id, rso.subj_osago_rec_id
  • Sort Method: external merge Disk: 1340816kB
3. 63,003.998 3,280,164.277 ↑ 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,706,742.929..3,280,164.277 rows=5,894,009 loops=1)

  • Filter: (((po.is_tech_cor = 0) AND (po.recall_policy_unq_id = 0) AND (po.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
4. 118,933.905 2,862,741.656 ↓ 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,706,741.484..2,862,741.656 rows=118,139,541 loops=1)

  • Hash Cond: (rls_only.glt_subj_osago_rec_id = rso.subj_osago_rec_id)
5. 37,132.852 37,132.852 ↑ 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.067..37,132.852 rows=19,392,939 loops=1)

  • Filter: ((is_tech_cor = 0) AND (recall_loss_rec_id = 0))
  • Rows Removed by Filter: 16237341
6. 52,186.492 2,706,674.899 ↓ 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,706,674.898..2,706,674.899 rows=114,099,538 loops=1)

  • Buckets: 1048576 (originally 1048576) Batches: 64 (originally 16) Memory Usage: 516097kB
7. 53,169.252 2,654,488.407 ↓ 12.5 114,099,538 1

Hash Right Join (cost=61,075,785.84..62,588,132.62 rows=9,116,710 width=468) (actual time=2,601,512.933..2,654,488.407 rows=114,099,538 loops=1)

  • Hash Cond: (rls.glt_policy_subj_info_id = psi.policy_subj_info_id)
8. 3,280.542 3,853.200 ↑ 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=708.566..3,853.200 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
9. 572.658 572.658 ↑ 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=572.658..572.658 rows=5,837,799 loops=1)

  • Index Cond: (glt_subj_osago_rec_id IS NULL)
10. 49,015.607 2,597,465.955 ↓ 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,597,465.955..2,597,465.955 rows=114,099,538 loops=1)

  • Buckets: 1048576 (originally 1048576) Batches: 8192 (originally 16) Memory Usage: 26575189kB
11. 1,629,173.737 2,548,450.348 ↓ 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=179,035.667..2,548,450.348 rows=114,099,538 loops=1)

  • Hash Cond: (psi.subj_osago_rec_id = rso.subj_osago_rec_id)
12. 740,587.060 740,587.060 ↑ 1.0 2,414,460,997 1

Seq Scan on policy_subj_info psi (cost=0.00..58,199,487.64 rows=2,414,842,880 width=24) (actual time=1.081..740,587.060 rows=2,414,460,997 loops=1)

13. 61,197.628 178,689.551 ↓ 102.7 107,966,181 1

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

  • Buckets: 2097152 (originally 2097152) Batches: 64 (originally 1) Memory Usage: 507905kB
14. 17,410.581 117,491.923 ↓ 102.7 107,966,181 1

Nested Loop (cost=0.11..414,708.79 rows=1,051,108 width=448) (actual time=0.043..117,491.923 rows=107,966,181 loops=1)

15. 7.312 7.312 ↓ 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.012..7.312 rows=41,370 loops=1)

16. 100,074.030 100,074.030 ↓ 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.006..2.419 rows=2,610 loops=41,370)

  • Index Cond: (subj_osago_doc_rec_id = so.doc_rec_id)
17. 354,418.623 354,418.623 ↓ 0.0 0 118,139,541

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

  • Index Cond: (policy_rec_id = psi.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 : 11.582 ms
Execution time : 3,355,443.809 ms