explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y2jn

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 26,062.292 ↑ 39,367,445.0 1 1

Unique (cost=26,032,743.56..27,016,929.68 rows=39,367,445 width=513) (actual time=26,062.283..26,062.292 rows=1 loops=1)

2.          

CTE cte_p

3. 0.082 0.082 ↑ 33.3 3 1

Function Scan on jsonb_to_recordset f_1 (cost=0.00..2.75 rows=100 width=32) (actual time=0.053..0.082 rows=3 loops=1)

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

CTE le

5. 0.079 0.429 ↑ 289,509.0 48 1

Nested Loop (cost=1,245.99..5,893,912.10 rows=13,896,434 width=866) (actual time=0.146..0.429 rows=48 loops=1)

6. 0.086 0.086 ↑ 33.3 3 1

CTE Scan on cte_p (cost=0.00..2.00 rows=100 width=32) (actual time=0.055..0.086 rows=3 loops=1)

7. 0.123 0.264 ↑ 8,685.2 16 3

Bitmap Heap Scan on legal_entity_actual a_1 (cost=1,245.99..57,549.46 rows=138,964 width=834) (actual time=0.058..0.088 rows=16 loops=3)

  • Recheck Cond: (((('{}'::text[] || inn) || ogrn) || kpp) @> cte_p.prm_arr)
  • Heap Blocks: exact=48
8. 0.141 0.141 ↑ 8,685.2 16 3

Bitmap Index Scan on idx_legal_entity_actual_arr (cost=0.00..1,211.25 rows=138,964 width=0) (actual time=0.047..0.047 rows=16 loops=3)

  • Index Cond: (((('{}'::text[] || inn) || ogrn) || kpp) @> cte_p.prm_arr)
9. 0.024 26,062.282 ↑ 19,683,722.5 2 1

Sort (cost=20,138,828.70..20,237,247.31 rows=39,367,445 width=513) (actual time=26,062.282..26,062.282 rows=2 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: 26kB
10. 0.025 26,062.258 ↑ 19,683,722.5 2 1

Hash Left Join (cost=5,503,307.15..6,676,230.89 rows=39,367,445 width=513) (actual time=25,925.417..26,062.258 rows=2 loops=1)

  • Hash Cond: (e.status_code = f.code)
11. 44.059 26,062.046 ↑ 19,683,722.5 2 1

Merge Join (cost=5,503,294.97..6,134,916.34 rows=39,367,445 width=302) (actual time=25,925.208..26,062.046 rows=2 loops=1)

  • Merge Cond: (b.spark_id = a.spark_id)
12. 2,292.311 3,135.060 ↑ 2.7 471,555 1

Sort (cost=220,648.22..223,832.52 rows=1,273,722 width=174) (actual time=3,042.071..3,135.060 rows=471,555 loops=1)

  • Sort Key: b.spark_id
  • Sort Method: external merge Disk: 233,816kB
13. 599.876 842.749 ↑ 1.0 1,273,722 1

Hash Left Join (cost=1.04..91,488.86 rows=1,273,722 width=174) (actual time=0.030..842.749 rows=1,273,722 loops=1)

  • Hash Cond: (b.reorganization_role_code = d.code)
14. 242.862 242.862 ↑ 1.0 1,273,722 1

Seq Scan on predecessor_successor_actual b (cost=0.00..81,059.22 rows=1,273,722 width=155) (actual time=0.009..242.862 rows=1,273,722 loops=1)

15. 0.004 0.011 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=27) (actual time=0.011..0.011 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.007 0.007 ↑ 1.0 2 1

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

17. 0.039 22,882.927 ↑ 289,509.0 48 1

Materialize (cost=5,282,646.75..5,352,128.92 rows=13,896,434 width=136) (actual time=22,882.872..22,882.927 rows=48 loops=1)

18. 0.119 22,882.888 ↑ 289,509.0 48 1

Sort (cost=5,282,646.75..5,317,387.84 rows=13,896,434 width=136) (actual time=22,882.861..22,882.888 rows=48 loops=1)

  • Sort Key: a.spark_id
  • Sort Method: quicksort Memory: 40kB
19. 9,305.463 22,882.769 ↑ 289,509.0 48 1

Hash Left Join (cost=1,162,477.09..2,751,860.39 rows=13,896,434 width=136) (actual time=13,624.038..22,882.769 rows=48 loops=1)

  • Hash Cond: (a.spark_id = e.spark_id)
20. 0.530 0.530 ↑ 289,509.0 48 1

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

21. 6,806.299 13,576.776 ↓ 1.0 27,787,287 1

Hash (cost=706,593.04..706,593.04 rows=27,787,204 width=8) (actual time=13,576.776..13,576.776 rows=27,787,287 loops=1)

  • Buckets: 8,388,608 Batches: 8 Memory Usage: 201,144kB
22. 6,770.477 6,770.477 ↓ 1.0 27,787,287 1

Seq Scan on status_actual e (cost=0.00..706,593.04 rows=27,787,204 width=8) (actual time=0.043..6,770.477 rows=27,787,287 loops=1)

23. 0.101 0.187 ↑ 1.0 186 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
24. 0.086 0.086 ↑ 1.0 186 1

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

Planning time : 0.970 ms
Execution time : 26,161.606 ms