explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HnFIG

Settings
# exclusive inclusive rows x rows loops node
1. 3,759.872 21,449.454 ↑ 6,608.4 90 1

GroupAggregate (cost=25,617,389.63..25,831,503.23 rows=594,760 width=53) (actual time=17,544.960..21,449.454 rows=90 loops=1)

  • Group Key: file_data.id_infra_instan, file_data.tx_file_type, file_data.id_enterprise, file_data._week
2.          

CTE week_series

3. 0.038 0.038 ↑ 500.0 2 1

Result (cost=0.00..5.04 rows=1,000 width=4) (actual time=0.022..0.038 rows=2 loops=1)

4. 417.051 17,689.582 ↑ 35.7 166,760 1

Sort (cost=25,617,384.59..25,632,253.59 rows=5,947,600 width=93) (actual time=17,519.609..17,689.582 rows=166,760 loops=1)

  • Sort Key: file_data.id_infra_instan, file_data.tx_file_type, file_data.id_enterprise, file_data._week
  • Sort Method: quicksort Memory: 48,114kB
5. 315.352 17,272.531 ↑ 35.7 166,760 1

Subquery Scan on file_data (cost=23,840,306.82..24,643,230.32 rows=5,947,600 width=93) (actual time=15,784.563..17,272.531 rows=166,760 loops=1)

6. 637.003 16,957.179 ↑ 35.7 166,760 1

Unique (cost=23,840,306.82..24,583,754.32 rows=5,947,600 width=101) (actual time=15,784.560..16,957.179 rows=166,760 loops=1)

7. 13,712.596 16,320.176 ↑ 204.9 483,858 1

Sort (cost=23,840,306.82..24,088,122.65 rows=99,126,333 width=101) (actual time=15,784.554..16,320.176 rows=483,858 loops=1)

  • Sort Key: week_series._week, po_flart.tx_working_file, po_flart.id_tseries DESC
  • Sort Method: quicksort Memory: 133,599kB
8. 985.976 2,607.580 ↑ 204.9 483,858 1

Nested Loop (cost=0.00..5,253,987.86 rows=99,126,333 width=101) (actual time=27.198..2,607.580 rows=483,858 loops=1)

  • Join Filter: (po_flart.id_tseries <= week_series._week)
9. 0.048 0.048 ↑ 500.0 2 1

CTE Scan on week_series (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.026..0.048 rows=2 loops=1)

10. 784.225 1,621.556 ↑ 1.2 241,929 2

Materialize (cost=0.00..794,026.31 rows=297,379 width=97) (actual time=13.584..810.778 rows=241,929 loops=2)

11. 464.518 837.331 ↑ 1.2 241,929 1

Append (cost=0.00..792,539.41 rows=297,379 width=97) (actual time=27.161..837.331 rows=241,929 loops=1)

12. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on po_flart (cost=0.00..0.00 rows=1 width=134) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: ((id_enterprise = 83) AND (id_infra_instan = 55,391))
13. 347.061 372.809 ↑ 1.2 241,929 1

Bitmap Heap Scan on po_flart_83 (cost=22,141.24..792,539.41 rows=297,378 width=97) (actual time=27.152..372.809 rows=241,929 loops=1)

  • Recheck Cond: (id_infra_instan = 55,391)
  • Filter: (id_enterprise = 83)
  • Heap Blocks: exact=9,071
14. 25.748 25.748 ↑ 1.2 241,929 1

Bitmap Index Scan on index_po_flart_83_id_infra_instan_id_screntry_idx (cost=0.00..22,066.90 rows=297,378 width=0) (actual time=25.747..25.748 rows=241,929 loops=1)

  • Index Cond: (id_infra_instan = 55,391)
Planning time : 4.113 ms
Execution time : 21,479.071 ms