explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CmuA

Settings
# exclusive inclusive rows x rows loops node
1. 0.069 316,250.907 ↑ 1.0 25 1

Limit (cost=32,942,378.68..32,942,378.74 rows=25 width=1,397) (actual time=316,250.847..316,250.907 rows=25 loops=1)

2.          

CTE a

3. 0.000 216,104.534 ↑ 1.1 42,118,693 1

Gather (cost=152,891.17..28,725,533.69 rows=44,569,557 width=168) (actual time=105,383.503..216,104.534 rows=42,118,693 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 3,013.458 230,107.158 ↑ 1.3 14,039,564 3 / 3

Parallel Hash Anti Join (cost=151,891.17..24,267,577.99 rows=18,570,649 width=168) (actual time=105,368.710..230,107.158 rows=14,039,564 loops=3)

  • Hash Cond: (a_1.id = b.id)
5. 227,091.898 227,091.898 ↑ 1.3 14,039,564 3 / 3

Parallel Seq Scan on xxtx_nm_accounting_v a_1 (cost=0.00..23,881,232.38 rows=18,570,649 width=168) (actual time=105,366.622..227,091.898 rows=14,039,564 loops=3)

  • Filter: ((first_rev <= 25,770) AND (last_rev > 25770) AND ((period)::text = ANY ('{"январь 2019","февраль 2019","март 2019"}'::text[])) AND ((filial)::text = ANY ('{01XXX,013XX,017XX,011XX,01000,01001,01004,015XX,012XX,016XX,014XX,01002,01200,01212,01113,01605,01002,01500,01606,01106,01700,01409,01205,01508,01701,01211,01313,01411,01302,01114,01003,01116,01400,01603,01405,01502,01501,01303,01308,01504,01102,01112,01706,01408,01307,01505,01403,01611,01607,01311,01402,01410,01111,01004,01300,01001,01705,01412,01101,01215,01507,01312,01204,01201,01704,01100,01404,01306,01304,01610,01000,01203,01309,01107,01703,01108,01109,01110,01702,01407,01310,01115,01608,01105,01600,01609,01708,01104,01305,01202}'::text[])))
  • Rows Removed by Filter: 71,337,911
6. 0.023 1.802 ↓ 0.0 0 3 / 3

Parallel Hash (cost=151,891.16..151,891.16 rows=1 width=8) (actual time=1.800..1.802 rows=0 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
7. 1.779 1.779 ↓ 0.0 0 1 / 3

Parallel Index Scan using xxtx_nm_accounting_v_fil_s2_cs2_idx on xxtx_nm_accounting_v b (cost=0.70..151,891.16 rows=1 width=8) (actual time=5.338..5.338 rows=0 loops=1)

  • Index Cond: (((filial)::text = ANY ('{01XXX,013XX,017XX,011XX,01000,01001,01004,015XX,012XX,016XX,014XX,01002,01200,01212,01113,01605,01002,01500,01606,01106,01700,01409,01205,01508,01701,01211,01313,01411,01302,01114,01003,01116,01400,01603,01405,01502,01501,01303,01308,01504,01102,01112,01706,01408,01307,01505,01403,01611,01607,01311,01402,01410,01111,01004,01300,01001,01705,01412,01101,01215,01507,01312,01204,01201,01704,01100,01404,01306,01304,01610,01000,01203,01309,01107,01703,01108,01109,01110,01702,01407,01310,01115,01608,01105,01600,01609,01708,01104,01305,01202}'::text[])) AND ((segment2)::text ~>=~ '911071'::text) AND ((segment2)::text ~<~ '911072'::text) AND ((segment2)::text ~>=~ '911072'::text) AND ((segment2)::text ~<~ '911073'::text) AND ((segment2)::text ~>=~ '911073'::text) AND ((segment2)::text ~<~ '911074'::text) AND ((segment2)::text ~>=~ '911074'::text) AND ((segment2)::text ~<~ '911075'::text) AND ((segment2)::text ~>=~ '911077'::text) AND ((segment2)::text ~<~ '911078'::text) AND ((segment2)::text ~>=~ '912071'::text) AND ((segment2)::text ~<~ '912072'::text) AND ((segment2)::text ~>=~ '912072'::text) AND ((segment2)::text ~<~ '912073'::text) AND ((segment2)::text ~>=~ '912073'::text) AND ((segment2)::text ~<~ '912074'::text) AND ((segment2)::text ~>=~ '912074'::text) AND ((segment2)::text ~<~ '912075'::text) AND ((segment2)::text ~>=~ '912077'::text) AND ((segment2)::text ~<~ '912078'::text) AND ((corr_segment2)::text ~>=~ '911071'::text) AND ((corr_segment2)::text ~<~ '911072'::text) AND ((corr_segment2)::text ~>=~ '911072'::text) AND ((corr_segment2)::text ~<~ '911073'::text) AND ((corr_segment2)::text ~>=~ '911073'::text) AND ((corr_segment2)::text ~<~ '911074'::text) AND ((corr_segment2)::text ~>=~ '911074'::text) AND ((corr_segment2)::text ~<~ '911075'::text) AND ((corr_segment2)::text ~>=~ '911077'::text) AND ((corr_segment2)::text ~<~ '911078'::text) AND ((corr_segment2)::text ~>=~ '912071'::text) AND ((corr_segment2)::text ~<~ '912072'::text) AND ((corr_segment2)::text ~>=~ '912072'::text) AND ((corr_segment2)::text ~<~ '912073'::text) AND ((corr_segment2)::text ~>=~ '912073'::text) AND ((corr_segment2)::text ~<~ '912074'::text) AND ((corr_segment2)::text ~>=~ '912074'::text) AND ((corr_segment2)::text ~<~ '912075'::text) AND ((corr_segment2)::text ~>=~ '912077'::text) AND ((corr_segment2)::text ~<~ '912078'::text))
  • Filter: (((segment2)::text ~~ '911071%'::text) AND ((segment2)::text ~~ '911072%'::text) AND ((segment2)::text ~~ '911073%'::text) AND ((segment2)::text ~~ '911074%'::text) AND ((segment2)::text ~~ '911077%'::text) AND ((segment2)::text ~~ '912071%'::text) AND ((segment2)::text ~~ '912072%'::text) AND ((segment2)::text ~~ '912073%'::text) AND ((segment2)::text ~~ '912074%'::text) AND ((segment2)::text ~~ '912077%'::text) AND ((corr_segment2)::text ~~ '911071%'::text) AND ((corr_segment2)::text ~~ '911072%'::text) AND ((corr_segment2)::text ~~ '911073%'::text) AND ((corr_segment2)::text ~~ '911074%'::text) AND ((corr_segment2)::text ~~ '911077%'::text) AND ((corr_segment2)::text ~~ '912071%'::text) AND ((corr_segment2)::text ~~ '912072%'::text) AND ((corr_segment2)::text ~~ '912073%'::text) AND ((corr_segment2)::text ~~ '912074%'::text) AND ((corr_segment2)::text ~~ '912077%'::text) AND (first_rev <= 25,770) AND (last_rev > 25770) AND ((period)::text = ANY ('{"январь 2019","февраль 2019","март 2019"}'::text[])))
8. 1,339.099 316,250.838 ↑ 32,946.0 50 1

Sort (cost=4,216,844.93..4,220,963.19 rows=1,647,302 width=1,397) (actual time=316,250.829..316,250.838 rows=50 loops=1)

  • Sort Key: a.id
  • Sort Method: top-N heapsort Memory: 77kB
9. 624.330 314,911.739 ↑ 1.2 1,370,928 1

Hash Left Join (cost=1,739,801.29..4,162,122.74 rows=1,647,302 width=1,397) (actual time=141,821.351..314,911.739 rows=1,370,928 loops=1)

  • Hash Cond: ((a.corr_segment4)::text = (cst.s_code)::text)
10. 560.821 314,286.896 ↑ 1.2 1,370,928 1

Hash Left Join (cost=1,739,766.42..4,139,437.47 rows=1,647,302 width=1,760) (actual time=141,820.766..314,286.896 rows=1,370,928 loops=1)

  • Hash Cond: ((a.corr_segment3)::text = (csi.s_code)::text)
11. 575.119 313,723.565 ↑ 1.2 1,370,928 1

Hash Left Join (cost=1,739,613.42..4,116,634.06 rows=1,647,302 width=1,664) (actual time=141,818.170..313,723.565 rows=1,370,928 loops=1)

  • Hash Cond: ((a.corr_segment2)::text = (csa.s_code)::text)
12. 477.032 313,146.225 ↑ 1.2 1,370,928 1

Hash Left Join (cost=1,739,444.33..4,093,814.58 rows=1,647,302 width=1,550) (actual time=141,815.869..313,146.225 rows=1,370,928 loops=1)

  • Hash Cond: ((a.segment4)::text = (st.s_code)::text)
13. 521.120 312,668.522 ↑ 1.2 1,370,928 1

Hash Left Join (cost=1,739,409.46..4,071,129.30 rows=1,647,302 width=1,366) (actual time=141,815.146..312,668.522 rows=1,370,928 loops=1)

  • Hash Cond: ((a.segment3)::text = (si.s_code)::text)
14. 553.871 312,144.841 ↑ 1.2 1,370,928 1

Hash Left Join (cost=1,739,256.46..4,048,325.90 rows=1,647,302 width=1,270) (actual time=141,812.177..312,144.841 rows=1,370,928 loops=1)

  • Hash Cond: ((a.segment2)::text = (sa.s_code)::text)
15. 2,876.170 311,588.209 ↑ 1.2 1,370,928 1

Hash Left Join (cost=1,739,087.37..4,025,506.41 rows=1,647,302 width=1,156) (actual time=141,809.330..311,588.209 rows=1,370,928 loops=1)

  • Hash Cond: (a.contract_id = c.doc_id)
16. 12,880.931 308,039.071 ↑ 1.2 1,370,928 1

Hash Left Join (cost=1,532,119.44..3,342,119.29 rows=1,647,302 width=1,125) (actual time=141,122.810..308,039.071 rows=1,370,928 loops=1)

  • Hash Cond: ((a.doc_id = d.doc_id) AND ((a.doc_type)::text = (d.doc_type)::text))
17. 9,650.003 259,543.800 ↑ 1.2 1,370,928 1

Hash Join (cost=8,586.18..1,083,586.17 rows=1,647,302 width=1,101) (actual time=105,468.080..259,543.800 rows=1,370,928 loops=1)

  • Hash Cond: (a.party_id = p.party_id)
18. 249,809.584 249,809.584 ↑ 1.1 42,118,693 1

CTE Scan on a (cost=0.00..891,391.14 rows=44,569,557 width=1,074) (actual time=105,383.532..249,809.584 rows=42,118,693 loops=1)

19. 4.555 84.213 ↑ 1.0 9,268 1

Hash (cost=8,468.98..8,468.98 rows=9,376 width=65) (actual time=84.213..84.213 rows=9,268 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 942kB
20. 79.658 79.658 ↑ 1.0 9,268 1

Seq Scan on xxtx_nm_parties_v p (cost=0.00..8,468.98 rows=9,376 width=65) (actual time=0.065..79.658 rows=9,268 loops=1)

  • Filter: ((inn)::text ~~ '77%'::text)
  • Rows Removed by Filter: 244,410
21. 11,805.463 35,614.340 ↑ 1.0 21,208,769 1

Hash (cost=1,018,816.16..1,018,816.16 rows=21,216,340 width=44) (actual time=35,614.340..35,614.340 rows=21,208,769 loops=1)

  • Buckets: 131,072 Batches: 256 Memory Usage: 7,273kB
22. 23,808.877 23,808.877 ↑ 1.0 21,208,769 1

Seq Scan on xxtx_nm_documents_v d (cost=0.00..1,018,816.16 rows=21,216,340 width=44) (actual time=2.281..23,808.877 rows=21,208,769 loops=1)

  • Filter: ((first_rev <= 2,673,823) AND (last_rev > 2673823))
  • Rows Removed by Filter: 27,550
23. 223.060 672.968 ↑ 1.1 476,565 1

Hash (cost=196,293.71..196,293.71 rows=525,458 width=38) (actual time=672.968..672.968 rows=476,565 loops=1)

  • Buckets: 262,144 Batches: 8 Memory Usage: 6,438kB
24. 449.908 449.908 ↑ 1.1 476,565 1

Index Scan using xxtx_nm_documents_v_doc_type_idx on xxtx_nm_documents_v c (cost=0.56..196,293.71 rows=525,458 width=38) (actual time=0.138..449.908 rows=476,565 loops=1)

  • Index Cond: ((doc_type)::text = 'XXCN009_DOC_HEADERS'::text)
  • Filter: ((first_rev <= 2,673,823) AND (last_rev > 2673823))
  • Rows Removed by Filter: 11,719
25. 1.502 2.761 ↑ 1.0 3,426 1

Hash (cost=126.26..126.26 rows=3,426 width=125) (actual time=2.760..2.761 rows=3,426 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 561kB
26. 1.259 1.259 ↑ 1.0 3,426 1

Seq Scan on xxtx_nm_spr_s02_accounts_v sa (cost=0.00..126.26 rows=3,426 width=125) (actual time=0.031..1.259 rows=3,426 loops=1)

27. 1.495 2.561 ↑ 1.0 3,689 1

Hash (cost=106.89..106.89 rows=3,689 width=104) (actual time=2.560..2.561 rows=3,689 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 526kB
28. 1.066 1.066 ↑ 1.0 3,689 1

Seq Scan on xxtx_nm_spr_s03_items_v si (cost=0.00..106.89 rows=3,689 width=104) (actual time=0.023..1.066 rows=3,689 loops=1)

29. 0.388 0.671 ↑ 1.0 661 1

Hash (cost=26.61..26.61 rows=661 width=190) (actual time=0.671..0.671 rows=661 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 153kB
30. 0.283 0.283 ↑ 1.0 661 1

Seq Scan on xxtx_nm_spr_s04_tax_v st (cost=0.00..26.61 rows=661 width=190) (actual time=0.038..0.283 rows=661 loops=1)

31. 1.538 2.221 ↑ 1.0 3,426 1

Hash (cost=126.26..126.26 rows=3,426 width=125) (actual time=2.221..2.221 rows=3,426 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 561kB
32. 0.683 0.683 ↑ 1.0 3,426 1

Seq Scan on xxtx_nm_spr_s02_accounts_v csa (cost=0.00..126.26 rows=3,426 width=125) (actual time=0.013..0.683 rows=3,426 loops=1)

33. 1.849 2.510 ↑ 1.0 3,689 1

Hash (cost=106.89..106.89 rows=3,689 width=104) (actual time=2.510..2.510 rows=3,689 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 526kB
34. 0.661 0.661 ↑ 1.0 3,689 1

Seq Scan on xxtx_nm_spr_s03_items_v csi (cost=0.00..106.89 rows=3,689 width=104) (actual time=0.014..0.661 rows=3,689 loops=1)

35. 0.373 0.513 ↑ 1.0 661 1

Hash (cost=26.61..26.61 rows=661 width=190) (actual time=0.513..0.513 rows=661 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 153kB
36. 0.140 0.140 ↑ 1.0 661 1

Seq Scan on xxtx_nm_spr_s04_tax_v cst (cost=0.00..26.61 rows=661 width=190) (actual time=0.013..0.140 rows=661 loops=1)

Planning time : 6.185 ms
Execution time : 316,406.524 ms