explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ICvq

Settings
# exclusive inclusive rows x rows loops node
1. 3,627.210 189,745.377 ↑ 1.0 1 1

Aggregate (cost=873,420.13..873,420.14 rows=1 width=48) (actual time=189,745.374..189,745.377 rows=1 loops=1)

2.          

CTE infra_col_data

3. 226.444 3,238.180 ↓ 1.4 82,454 1

Unique (cost=32,822.45..33,108.63 rows=57,236 width=174) (actual time=2,862.797..3,238.180 rows=82,454 loops=1)

4. 269.109 3,011.736 ↓ 1.5 84,268 1

Sort (cost=32,822.45..32,965.54 rows=57,236 width=174) (actual time=2,862.792..3,011.736 rows=84,268 loops=1)

  • Sort Key: po_infra_instan.id_infra_instan
  • Sort Method: quicksort Memory: 20177kB
5. 171.944 2,742.627 ↓ 1.5 84,268 1

Hash Join (cost=20,629.44..28,299.48 rows=57,236 width=174) (actual time=1,649.525..2,742.627 rows=84,268 loops=1)

  • Hash Cond: (po_project_sub.id_project = po_project.id_project)
6. 175.240 2,372.908 ↓ 1.5 84,268 1

Hash Join (cost=19,150.00..26,033.04 rows=57,236 width=170) (actual time=1,451.703..2,372.908 rows=84,268 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_project_sub = po_project_sub.id_project_sub)
7. 424.546 1,931.640 ↓ 1.5 84,268 1

Hash Join (cost=16,299.65..22,395.69 rows=57,236 width=166) (actual time=1,185.475..1,931.640 rows=84,268 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_infra_instan = po_infra_instan.id_infra_instan)
8. 345.968 345.968 ↓ 1.0 274,533 1

Seq Scan on po_proj_sub_infra (cost=0.00..4,494.22 rows=274,522 width=8) (actual time=23.725..345.968 rows=274,533 loops=1)

9. 115.745 1,161.126 ↓ 1.5 84,771 1

Hash (cost=15,583.75..15,583.75 rows=57,272 width=162) (actual time=1,161.124..1,161.126 rows=84,771 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 12693kB
10. 358.411 1,045.381 ↓ 1.5 84,771 1

Hash Join (cost=20.84..15,583.75 rows=57,272 width=162) (actual time=61.211..1,045.381 rows=84,771 loops=1)

  • Hash Cond: (po_infra_instan.id_connector_details = po_connector_details.id_connector_details)
11. 645.934 645.934 ↑ 1.0 274,442 1

Seq Scan on po_infra_instan (cost=0.00..13,960.92 rows=274,472 width=162) (actual time=20.111..645.934 rows=274,442 loops=1)

  • Filter: (NOT bl_marked_for_deletion)
  • Rows Removed by Filter: 233
12. 0.079 41.036 ↑ 1.0 53 1

Hash (cost=20.18..20.18 rows=53 width=4) (actual time=41.035..41.036 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 40.957 40.957 ↑ 1.0 53 1

Seq Scan on po_connector_details (cost=0.00..20.18 rows=53 width=4) (actual time=19.800..40.957 rows=53 loops=1)

  • Filter: (id_enterprise = 83)
  • Rows Removed by Filter: 202
14. 92.524 266.028 ↓ 1.0 85,261 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 4355kB
15. 173.504 173.504 ↓ 1.0 85,261 1

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

16. 31.231 197.775 ↓ 1.0 29,343 1

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

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

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

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

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

19. 0.319 0.640 ↑ 1.0 302 1

Hash (cost=7.02..7.02 rows=302 width=4) (actual time=0.639..0.640 rows=302 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
20. 0.321 0.321 ↑ 1.0 302 1

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

21. 4,050.960 186,118.167 ↓ 8.1 1,363,606 1

Nested Loop (cost=0.56..832,713.61 rows=168,842 width=4) (actual time=3,009.684..186,118.167 rows=1,363,606 loops=1)

22. 3,471.843 3,471.843 ↓ 1.4 82,454 1

CTE Scan on infra_col_data (cost=0.00..1,144.72 rows=57,236 width=4) (actual time=2,862.802..3,471.843 rows=82,454 loops=1)

23. 178,595.364 178,595.364 ↓ 5.7 17 82,454

Index Scan using idx_po_import_request_83_ts_status_last_updated_at_infra on po_import_request_83 _req (cost=0.56..14.50 rows=3 width=12) (actual time=0.167..2.166 rows=17 loops=82,454)

  • Index Cond: ((id_infra_instan = infra_col_data.id_infra_instan) AND ((ts_status_last_updated_at)::date >= '2018-12-01'::date) AND ((ts_status_last_updated_at)::date <= '2019-01-01'::date))
Planning time : 388.963 ms
Execution time : 189,752.770 ms