explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C1Sh

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 3,620.610 ↓ 0.0 0 1

Sort (cost=1,526,088.25..1,526,089.32 rows=428 width=20) (actual time=3,620.609..3,620.610 rows=0 loops=1)

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

CTE infra_col

3. 130.139 2,684.190 ↑ 73,606.0 1 1

HashAggregate (cost=68,913.87..69,649.93 rows=73,606 width=4) (actual time=2,684.189..2,684.190 rows=1 loops=1)

  • Group Key: infra.id_infra_instan
4. 579.131 2,554.051 ↓ 1.4 102,395 1

Hash Join (cost=15,748.68..68,729.86 rows=73,606 width=4) (actual time=1,437.266..2,554.051 rows=102,395 loops=1)

  • Hash Cond: (infra.id_infra_instan = po_proj_sub_infra.id_infra_instan)
5. 537.810 537.810 ↓ 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.008..537.810 rows=432,337 loops=1)

  • Filter: (NOT bl_marked_for_deletion)
  • Rows Removed by Filter: 370
6. 110.615 1,437.110 ↓ 1.4 102,830 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,640kB
7. 535.368 1,326.495 ↓ 1.4 102,830 1

Hash Join (cost=5,159.92..14,827.83 rows=73,668 width=4) (actual time=372.066..1,326.495 rows=102,830 loops=1)

  • Hash Cond: (po_proj_sub_infra.id_project_sub = po_project_sub.id_project_sub)
8. 419.967 419.967 ↓ 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.008..419.967 rows=443,938 loops=1)

9. 9.598 371.160 ↑ 2.6 8,967 1

Hash (cost=4,869.00..4,869.00 rows=23,274 width=4) (actual time=371.159..371.160 rows=8,967 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 572kB
10. 145.819 361.562 ↑ 2.6 8,967 1

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

  • Hash Cond: (po_project_sub.id_project = po_project.id_project)
11. 133.878 133.878 ↑ 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.007..133.878 rows=140,174 loops=1)

12. 4.912 81.865 ↑ 1.3 4,746 1

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

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

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

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

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

15. 0.071 0.210 ↑ 1.1 69 1

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

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

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

  • Filter: (id_enterprise = 83)
  • Rows Removed by Filter: 346
17. 0.005 3,620.603 ↓ 0.0 0 1

HashAggregate (cost=1,456,409.98..1,456,419.61 rows=428 width=20) (actual time=3,620.602..3,620.603 rows=0 loops=1)

  • Group Key: (monthly_flart.id_tseries)::date
18. 36.328 3,620.598 ↓ 0.0 0 1

Hash Join (cost=724,168.76..1,300,267.80 rows=15,614,218 width=12) (actual time=3,620.597..3,620.598 rows=0 loops=1)

  • Hash Cond: (infra_col.id_infra_instan = monthly_flart.id_infra_instan)
19. 2,684.193 2,684.193 ↑ 73,606.0 1 1

CTE Scan on infra_col (cost=0.00..1,472.12 rows=73,606 width=4) (actual time=2,684.192..2,684.193 rows=1 loops=1)

20. 0.014 900.077 ↓ 0.0 0 1

Hash (cost=620,715.85..620,715.85 rows=5,634,873 width=20) (actual time=900.076..900.077 rows=0 loops=1)

  • Buckets: 8,388,608 Batches: 2 Memory Usage: 65,536kB
21. 0.009 900.063 ↓ 0.0 0 1

Append (cost=64,704.68..620,715.85 rows=5,634,873 width=20) (actual time=900.062..900.063 rows=0 loops=1)

22. 180.162 474.633 ↓ 0.0 0 1

Bitmap Heap Scan on po_monthly_flart monthly_flart (cost=64,704.68..324,411.09 rows=2,779,438 width=20) (actual time=474.632..474.633 rows=0 loops=1)

  • Recheck Cond: ((id_tseries >= '2018-01-01'::date) AND (id_tseries <= '2018-12-31'::date))
  • Filter: (id_enterprise = 83)
  • Heap Blocks: exact=138,625
23. 294.471 294.471 ↑ 1.0 3,037,913 1

Bitmap Index Scan on index_monthly_to_id_tseries (cost=0.00..64,009.82 rows=3,052,538 width=0) (actual time=294.470..294.471 rows=3,037,913 loops=1)

  • Index Cond: ((id_tseries >= '2018-01-01'::date) AND (id_tseries <= '2018-12-31'::date))
24. 166.141 425.421 ↓ 0.0 0 1

Bitmap Heap Scan on po_monthly_flart_83 monthly_flart_1 (cost=69,056.65..296,304.76 rows=2,855,435 width=20) (actual time=425.420..425.421 rows=0 loops=1)

  • Recheck Cond: ((id_tseries >= '2018-01-01'::date) AND (id_tseries <= '2018-12-31'::date))
  • Filter: (id_enterprise = 83)
  • Heap Blocks: exact=128,467
25. 259.280 259.280 ↑ 1.0 2,832,716 1

Bitmap Index Scan on po_monthly_flart_83_id_tseries_idx (cost=0.00..68,342.79 rows=2,855,435 width=0) (actual time=259.279..259.280 rows=2,832,716 loops=1)

  • Index Cond: ((id_tseries >= '2018-01-01'::date) AND (id_tseries <= '2018-12-31'::date))
Planning time : 1.456 ms
Execution time : 3,626.386 ms