explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OGGM

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 1,766.727 ↑ 2,187,080.3 18 1

Unique (cost=23,037,117.24..24,021,303.37 rows=39,367,445 width=513) (actual time=1,766.706..1,766.727 rows=18 loops=1)

2.          

CTE cte_p

3. 0.109 0.109 ↑ 5.6 18 1

Function Scan on jsonb_to_recordset f_1 (cost=0.00..2.75 rows=100 width=32) (actual time=0.077..0.109 rows=18 loops=1)

  • Filter: ((inn IS NOT NULL) OR (ogrn IS NOT NULL))
4.          

CTE le

5. 0.073 2.279 ↑ 338,937.4 41 1

Nested Loop (cost=1,197.70..4,406,875.40 rows=13,896,434 width=866) (actual time=0.197..2.279 rows=41 loops=1)

6. 0.118 0.118 ↑ 5.6 18 1

CTE Scan on cte_p (cost=0.00..2.00 rows=100 width=32) (actual time=0.078..0.118 rows=18 loops=1)

7. 0.378 2.088 ↑ 69,482.0 2 18

Bitmap Heap Scan on legal_entity_actual a_1 (cost=1,197.70..42,679.09 rows=138,964 width=834) (actual time=0.105..0.116 rows=2 loops=18)

  • Recheck Cond: (((('{}'::text[] || inn) || ogrn) || kpp) @> cte_p.prm_arr)
  • Heap Blocks: exact=41
8. 1.710 1.710 ↑ 69,482.0 2 18

Bitmap Index Scan on idx_legal_entity_actual_arr (cost=0.00..1,162.96 rows=138,964 width=0) (actual time=0.095..0.095 rows=2 loops=18)

  • Index Cond: (((('{}'::text[] || inn) || ogrn) || kpp) @> cte_p.prm_arr)
9. 0.181 1,766.705 ↑ 1,230,232.7 32 1

Sort (cost=18,630,239.08..18,728,657.70 rows=39,367,445 width=513) (actual time=1,766.703..1,766.705 rows=32 loops=1)

  • Sort Key: a.inn, a.ogrn, a.kpp, d.type_name, b.inn, a.full_name_rus, f.status_name, b.ogrn, b.full_name
  • Sort Method: quicksort Memory: 46kB
10. 0.083 1,766.524 ↑ 1,230,232.7 32 1

Hash Left Join (cost=2,673,020.60..6,474,763.78 rows=39,367,445 width=513) (actual time=24.981..1,766.524 rows=32 loops=1)

  • Hash Cond: (e.status_code = f.code)
11. 0.725 1,766.227 ↑ 1,230,232.7 32 1

Merge Right Join (cost=2,673,008.41..5,933,449.22 rows=39,367,445 width=302) (actual time=24.715..1,766.227 rows=32 loops=1)

  • Merge Cond: (e.spark_id = a.spark_id)
12. 55.743 55.743 ↑ 4,505.8 6,167 1

Index Scan using idx_status_actual_spark_id on status_actual e (cost=0.44..1,738,556.62 rows=27,787,204 width=8) (actual time=0.047..55.743 rows=6,167 loops=1)

13. 0.054 1,709.759 ↑ 1,230,232.7 32 1

Materialize (cost=2,673,007.97..3,633,331.53 rows=39,367,445 width=302) (actual time=12.270..1,709.759 rows=32 loops=1)

14. 27.027 1,709.705 ↑ 1,230,232.7 32 1

Merge Join (cost=2,673,007.97..3,534,912.92 rows=39,367,445 width=302) (actual time=12.263..1,709.705 rows=32 loops=1)

  • Merge Cond: (b.spark_id = a.spark_id)
15. 326.949 1,680.153 ↑ 2.7 471,555 1

Nested Loop Left Join (cost=0.43..233,468.31 rows=1,273,722 width=174) (actual time=0.055..1,680.153 rows=471,555 loops=1)

  • Join Filter: (b.reorganization_role_code = d.code)
  • Rows Removed by Join Filter: 263,852
16. 1,353.204 1,353.204 ↑ 2.7 471,555 1

Index Scan using idx_predecessor_successor_actual_spark_id on predecessor_successor_actual b (cost=0.43..201,624.24 rows=1,273,722 width=155) (actual time=0.040..1,353.204 rows=471,555 loops=1)

17. 0.000 0.000 ↑ 1.0 2 471,555

Materialize (cost=0.00..1.03 rows=2 width=27) (actual time=0.000..0.000 rows=2 loops=471,555)

18. 0.008 0.008 ↑ 1.0 2 1

Seq Scan on dct_reorganization_role_actual d (cost=0.00..1.02 rows=2 width=27) (actual time=0.007..0.008 rows=2 loops=1)

19. 0.040 2.525 ↑ 252,662.4 55 1

Materialize (cost=2,673,007.55..2,742,489.72 rows=13,896,434 width=132) (actual time=2.458..2.525 rows=55 loops=1)

20. 0.110 2.485 ↑ 338,937.4 41 1

Sort (cost=2,673,007.55..2,707,748.63 rows=13,896,434 width=132) (actual time=2.455..2.485 rows=41 loops=1)

  • Sort Key: a.spark_id
  • Sort Method: quicksort Memory: 38kB
21. 2.375 2.375 ↑ 338,937.4 41 1

CTE Scan on le a (cost=0.00..277,928.68 rows=13,896,434 width=132) (actual time=0.202..2.375 rows=41 loops=1)

22. 0.084 0.214 ↑ 1.0 186 1

Hash (cost=9.86..9.86 rows=186 width=219) (actual time=0.213..0.214 rows=186 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
23. 0.130 0.130 ↑ 1.0 186 1

Seq Scan on dct_status_actual f (cost=0.00..9.86 rows=186 width=219) (actual time=0.021..0.130 rows=186 loops=1)

Planning time : 3.751 ms
Execution time : 1,767.144 ms