explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v3nh

Settings
# exclusive inclusive rows x rows loops node
1. 0.193 337,921.900 ↑ 6.9 63 1

Sort (cost=787,654.38..787,655.46 rows=433 width=20) (actual time=337,921.818..337,921.900 rows=63 loops=1)

  • Sort Key: ((monthly_flart.id_tseries)::date)
  • Sort Method: quicksort Memory: 29kB
2.          

CTE infra_col

3. 1,899.216 26,340.730 ↓ 1.4 100,580 1

HashAggregate (cost=68,913.87..69,649.93 rows=73,606 width=4) (actual time=25,359.440..26,340.730 rows=100,580 loops=1)

  • Group Key: infra.id_infra_instan
4. 4,521.335 24,441.514 ↓ 1.4 102,395 1

Hash Join (cost=15,748.68..68,729.86 rows=73,606 width=4) (actual time=13,161.254..24,441.514 rows=102,395 loops=1)

  • Hash Cond: (infra.id_infra_instan = po_proj_sub_infra.id_infra_instan)
5. 6,759.716 6,759.716 ↓ 1.0 432,337 1

Seq Scan on po_infra_instan infra (cost=0.00..49,543.40 rows=432,275 width=4) (actual time=0.010..6,759.716 rows=432,337 loops=1)

  • Filter: (NOT bl_marked_for_deletion)
  • Rows Removed by Filter: 370
6. 1,294.266 13,160.463 ↓ 1.4 102,830 1

Hash (cost=14,827.83..14,827.83 rows=73,668 width=4) (actual time=13,160.462..13,160.463 rows=102,830 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,640kB
7. 4,372.782 11,866.197 ↓ 1.4 102,830 1

Hash Join (cost=5,159.92..14,827.83 rows=73,668 width=4) (actual time=3,275.313..11,866.197 rows=102,830 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_project_sub = po_project_sub.id_project_sub)
8. 4,219.611 4,219.611 ↓ 1.0 443,938 1

Seq Scan on po_proj_sub_infra (cost=0.00..7,266.71 rows=443,871 width=8) (actual time=0.012..4,219.611 rows=443,938 loops=1)

9. 15.383 3,273.804 ↑ 2.6 8,967 1

Hash (cost=4,869.00..4,869.00 rows=23,274 width=4) (actual time=3,273.803..3,273.804 rows=8,967 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 572kB
10. 930.750 3,258.421 ↑ 2.6 8,967 1

Hash Join (cost=1,155.07..4,869.00 rows=23,274 width=4) (actual time=685.041..3,258.421 rows=8,967 loops=1)

  • Hash Cond: (po_project_sub.id_project = po_project.id_project)
11. 1,642.736 1,642.736 ↑ 1.0 140,174 1

Seq Scan on po_project_sub (cost=0.00..2,955.33 rows=140,233 width=8) (actual time=0.013..1,642.736 rows=140,174 loops=1)

12. 7.600 684.935 ↑ 1.3 4,746 1

Hash (cost=1,077.93..1,077.93 rows=6,171 width=4) (actual time=684.934..684.935 rows=4,746 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 231kB
13. 345.029 677.335 ↑ 1.3 4,746 1

Hash Join (cost=12.94..1,077.93 rows=6,171 width=4) (actual time=1.193..677.335 rows=4,746 loops=1)

  • Hash Cond: (po_project.id_organization = po_organization.id_organization)
14. 331.953 331.953 ↑ 1.0 37,144 1

Seq Scan on po_project (cost=0.00..863.84 rows=37,184 width=8) (actual time=0.009..331.953 rows=37,144 loops=1)

15. 0.116 0.353 ↑ 1.1 69 1

Hash (cost=11.95..11.95 rows=79 width=4) (actual time=0.352..0.353 rows=69 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
16. 0.237 0.237 ↑ 1.1 69 1

Seq Scan on po_organization (cost=0.00..11.95 rows=79 width=4) (actual time=0.042..0.237 rows=69 loops=1)

  • Filter: (id_enterprise = 83)
  • Rows Removed by Filter: 346
17. 22,183.658 337,921.707 ↑ 6.9 63 1

HashAggregate (cost=717,975.74..717,985.49 rows=433 width=20) (actual time=337,921.576..337,921.707 rows=63 loops=1)

  • Group Key: (monthly_flart.id_tseries)::date
18. 41,124.306 315,738.049 ↑ 3.0 2,364,469 1

Merge Join (cost=525,401.19..648,082.46 rows=6,989,328 width=12) (actual time=254,899.053..315,738.049 rows=2,364,469 loops=1)

  • Merge Cond: (infra_col.id_infra_instan = monthly_flart.id_infra_instan)
19. 1,942.358 29,670.485 ↓ 1.4 100,450 1

Sort (cost=7,422.26..7,606.27 rows=73,606 width=4) (actual time=28,777.880..29,670.485 rows=100,450 loops=1)

  • Sort Key: infra_col.id_infra_instan
  • Sort Method: quicksort Memory: 7,787kB
20. 27,728.127 27,728.127 ↓ 1.4 100,580 1

CTE Scan on infra_col (cost=0.00..1,472.12 rows=73,606 width=4) (actual time=25,359.447..27,728.127 rows=100,580 loops=1)

21. 36,261.215 244,943.258 ↓ 1.0 2,364,784 1

Sort (cost=517,978.94..523,852.91 rows=2,349,590 width=20) (actual time=226,121.142..244,943.258 rows=2,364,784 loops=1)

  • Sort Key: monthly_flart.id_infra_instan
  • Sort Method: quicksort Memory: 249,734kB
22. 174,045.565 208,682.043 ↓ 1.0 2,364,784 1

Bitmap Heap Scan on po_monthly_flart_83 monthly_flart (cost=56,823.74..269,345.59 rows=2,349,590 width=20) (actual time=34,858.396..208,682.043 rows=2,364,784 loops=1)

  • Recheck Cond: ((id_tseries >= '2018-01-01'::date) AND (id_tseries <= '2018-12-01'::date))
  • Heap Blocks: exact=125,922
23. 34,636.478 34,636.478 ↓ 1.0 2,364,784 1

Bitmap Index Scan on po_monthly_flart_83_id_tseries_idx (cost=0.00..56,236.34 rows=2,349,590 width=0) (actual time=34,636.477..34,636.478 rows=2,364,784 loops=1)

  • Index Cond: ((id_tseries >= '2018-01-01'::date) AND (id_tseries <= '2018-12-01'::date))
Planning time : 2.118 ms
Execution time : 338,264.015 ms