explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SEsi

Settings
# exclusive inclusive rows x rows loops node
1. 0.116 86,960.363 ↑ 1.0 50 1

Limit (cost=1,843,912.91..1,843,913.04 rows=50 width=1,678) (actual time=86,960.198..86,960.363 rows=50 loops=1)

2.          

CTE infra_col_data

3. 324.862 3,791.370 ↓ 1.2 151,150 1

Unique (cost=67,269.18..67,898.30 rows=125,823 width=174) (actual time=3,207.328..3,791.370 rows=151,150 loops=1)

4. 499.789 3,466.508 ↓ 1.2 152,964 1

Sort (cost=67,269.18..67,583.74 rows=125,823 width=174) (actual time=3,207.325..3,466.508 rows=152,964 loops=1)

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: external merge Disk: 20648kB
5. 311.396 2,966.719 ↓ 1.2 152,964 1

Hash Join (cost=25,459.67..45,859.33 rows=125,823 width=174) (actual time=1,271.859..2,966.719 rows=152,964 loops=1)

  • Hash Cond: (po_project_sub.id_project = po_project.id_project)
6. 529.984 2,536.446 ↓ 1.2 152,964 1

Hash Join (cost=23,980.23..42,649.82 rows=125,823 width=170) (actual time=1,152.937..2,536.446 rows=152,964 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_infra_instan = po_infra_instan.id_infra_instan)
7. 572.446 1,033.190 ↓ 1.0 274,531 1

Hash Join (cost=3,184.35..13,933.25 rows=274,522 width=12) (actual time=179.603..1,033.190 rows=274,531 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_project_sub = po_project_sub.id_project_sub)
8. 281.345 281.345 ↓ 1.0 274,531 1

Seq Scan on po_proj_sub_infra (cost=0.00..4,494.22 rows=274,522 width=8) (actual time=0.016..281.345 rows=274,531 loops=1)

9. 90.210 179.399 ↓ 1.0 85,261 1

Hash (cost=1,784.60..1,784.60 rows=85,260 width=8) (actual time=179.398..179.399 rows=85,261 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2697kB
10. 89.189 89.189 ↓ 1.0 85,261 1

Seq Scan on po_project_sub (cost=0.00..1,784.60 rows=85,260 width=8) (actual time=0.015..89.189 rows=85,261 loops=1)

11. 198.906 973.272 ↓ 1.2 153,555 1

Hash (cost=16,271.22..16,271.22 rows=125,893 width=162) (actual time=973.271..973.272 rows=153,555 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 2855kB
12. 416.263 774.366 ↓ 1.2 153,555 1

Hash Join (cost=22.61..16,271.22 rows=125,893 width=162) (actual time=0.413..774.366 rows=153,555 loops=1)

  • Hash Cond: (po_infra_instan.id_connector_details = po_connector_details.id_connector_details)
13. 357.716 357.716 ↓ 1.0 274,440 1

Seq Scan on po_infra_instan (cost=0.00..13,960.75 rows=274,382 width=162) (actual time=0.018..357.716 rows=274,440 loops=1)

  • Filter: (NOT bl_marked_for_deletion)
  • Rows Removed by Filter: 233
14. 0.120 0.387 ↑ 1.0 117 1

Hash (cost=21.14..21.14 rows=117 width=4) (actual time=0.386..0.387 rows=117 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
15. 0.267 0.267 ↑ 1.0 117 1

Seq Scan on po_connector_details (cost=0.00..21.14 rows=117 width=4) (actual time=0.015..0.267 rows=117 loops=1)

  • Filter: (id_enterprise = ANY ('{83,107,94,105,135}'::integer[]))
  • Rows Removed by Filter: 138
16. 29.579 118.877 ↓ 1.0 29,343 1

Hash (cost=1,112.67..1,112.67 rows=29,342 width=8) (actual time=118.876..118.877 rows=29,343 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1403kB
17. 58.522 89.298 ↓ 1.0 29,343 1

Hash Join (cost=10.79..1,112.67 rows=29,342 width=8) (actual time=0.668..89.298 rows=29,343 loops=1)

  • Hash Cond: (po_project.id_organization = org.id_organization)
18. 30.133 30.133 ↓ 1.0 29,343 1

Seq Scan on po_project (cost=0.00..698.42 rows=29,342 width=8) (actual time=0.017..30.133 rows=29,343 loops=1)

19. 0.330 0.643 ↓ 1.0 303 1

Hash (cost=7.02..7.02 rows=302 width=4) (actual time=0.642..0.643 rows=303 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
20. 0.313 0.313 ↓ 1.0 303 1

Seq Scan on po_organization org (cost=0.00..7.02 rows=302 width=4) (actual time=0.005..0.313 rows=303 loops=1)

21. 87.922 86,960.247 ↑ 165.8 50 1

Sort (cost=1,776,014.61..1,776,035.34 rows=8,291 width=1,678) (actual time=86,960.194..86,960.247 rows=50 loops=1)

  • Sort Key: _req.id_import_request DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 47kB
22. 464.233 86,872.325 ↓ 6.9 57,062 1

Nested Loop (cost=0.57..1,775,739.19 rows=8,291 width=1,678) (actual time=10,886.608..86,872.325 rows=57,062 loops=1)

23. 4,182.492 4,182.492 ↓ 1.2 151,150 1

CTE Scan on infra_col_data (cost=0.00..2,516.46 rows=125,823 width=1,576) (actual time=3,207.332..4,182.492 rows=151,150 loops=1)

24. 82,225.600 82,225.600 ↓ 0.0 0 151,150

Index Scan using idx_po_import_request_ts_status_last_updated_at_infra on po_import_request _req (cost=0.57..14.08 rows=1 width=106) (actual time=0.542..0.544 rows=0 loops=151,150)

  • Index Cond: ((id_infra_instan = infra_col_data.id_infra_instan) AND ((ts_status_last_updated_at)::date >= '2018-01-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
  • Filter: ((nu_extraction_status / 1000) = ANY ('{8,4,9,0,7}'::integer[]))
  • Rows Removed by Filter: 105
Planning time : 1.451 ms
Execution time : 86,971.428 ms