explain.depesz.com

PostgreSQL's explain analyze made readable

Result: raUd

Settings
# exclusive inclusive rows x rows loops node
1. 0.159 2,538.749 ↓ 419.0 419 1

GroupAggregate (cost=1,823.55..1,823.60 rows=1 width=121) (actual time=2,538.566..2,538.749 rows=419 loops=1)

  • Group Key: pd.portfolio_day, ph.portfolio_hour_ending, ph.repeated_hour_flag, tph.primary_key, l.te_rdfid, l.lodf, oe.equipment_name, oe.rating, oe.from_station, oe.from_station_kv, oe.to_station, oe.to_station_kv, oe.equipment_type
2. 0.254 2,538.590 ↓ 419.0 419 1

Sort (cost=1,823.55..1,823.55 rows=1 width=121) (actual time=2,538.560..2,538.590 rows=419 loops=1)

  • Sort Key: pd.portfolio_day, ph.portfolio_hour_ending, ph.repeated_hour_flag, tph.primary_key, l.te_rdfid, l.lodf, oe.equipment_name, oe.rating, oe.from_station, oe.from_station_kv, oe.to_station, oe.to_station_kv, oe.equipment_type
  • Sort Method: quicksort Memory: 136kB
3. 0.338 2,538.336 ↓ 419.0 419 1

Nested Loop (cost=1,815.47..1,823.54 rows=1 width=121) (actual time=2,230.112..2,538.336 rows=419 loops=1)

4. 136.386 2,535.967 ↓ 677.0 677 1

Unique (cost=1,815.20..1,815.22 rows=1 width=81) (actual time=2,229.855..2,535.967 rows=677 loops=1)

5. 993.790 2,399.581 ↓ 589,128.0 589,128 1

Sort (cost=1,815.20..1,815.20 rows=1 width=81) (actual time=2,229.854..2,399.581 rows=589,128 loops=1)

  • Sort Key: ((((((to_char((pd.portfolio_day)::timestamp with time zone, 'MM-DD-YYYY'::text) || ' '::text) || lpad((ph.portfolio_hour_ending)::text, 2, '0'::text)) || ':00'::text))::timestamp without time zone - '01:00:00'::interval)), (((((to_char((pd.portfolio_day)::timestamp with time zone, 'MM-DD-YYYY'::text) || ' '::text) || lpad((ph.portfolio_hour_ending)::text, 2, '0'::text)) || ':00'::text))::timestamp without time zone), ph.portfolio_hour_ending, ph.repeated_hour_flag, tph.primary_key, l.te_rdfid, l.lodf
  • Sort Method: external merge Disk: 56,232kB
6. 1,389.927 1,405.791 ↓ 589,128.0 589,128 1

Hash Join (cost=1,131.40..1,815.19 rows=1 width=81) (actual time=13.729..1,405.791 rows=589,128 loops=1)

  • Hash Cond: ((c.p_target_ibc_id)::numeric = tph.p_target_ibc_id)
7. 2.177 2.177 ↑ 1.0 16,248 1

Seq Scan on temp_api_constraints c (cost=0.00..602.48 rows=16,248 width=8) (actual time=0.006..2.177 rows=16,248 loops=1)

8. 0.139 13.687 ↓ 677.0 677 1

Hash (cost=1,131.39..1,131.39 rows=1 width=71) (actual time=13.687..13.687 rows=677 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 80kB
9. 0.249 13.548 ↓ 677.0 677 1

Hash Join (cost=1,086.76..1,131.39 rows=1 width=71) (actual time=13.344..13.548 rows=677 loops=1)

  • Hash Cond: (l.p_target_ph_id = tph.primary_key)
10. 0.088 0.088 ↑ 1.0 1,354 1

Seq Scan on p_lodf_107846 l (cost=0.00..39.54 rows=1,354 width=56) (actual time=0.003..0.088 rows=1,354 loops=1)

11. 2.850 13.211 ↓ 1.6 19,484 1

Hash (cost=929.73..929.73 rows=12,563 width=23) (actual time=13.211..13.211 rows=19,484 loops=1)

  • Buckets: 32,768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1,398kB
12. 2.835 10.361 ↓ 1.6 19,484 1

Nested Loop (cost=1.27..929.73 rows=12,563 width=23) (actual time=0.035..10.361 rows=19,484 loops=1)

13. 0.022 0.130 ↓ 3.9 86 1

Nested Loop (cost=0.70..124.52 rows=22 width=13) (actual time=0.019..0.130 rows=86 loops=1)

14. 0.018 0.018 ↓ 2.5 10 1

Index Scan using portfolio_day_idx2 on portfolio_day pd (cost=0.29..16.59 rows=4 width=8) (actual time=0.006..0.018 rows=10 loops=1)

  • Index Cond: (portfolio_day = '2020-06-23'::date)
15. 0.090 0.090 ↓ 1.5 9 10

Index Scan using portfolio_hour_idx on portfolio_hour ph (cost=0.42..26.92 rows=6 width=15) (actual time=0.006..0.009 rows=9 loops=10)

  • Index Cond: (p_portfolio_day_id = (pd.primary_key)::numeric)
  • Filter: (portfolio_hour_ending = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24}'::integer[]))
16. 7.396 7.396 ↑ 2.5 227 86

Index Scan using p_target_ph_idx on p_target_ph tph (cost=0.56..30.90 rows=570 width=20) (actual time=0.012..0.086 rows=227 loops=86)

  • Index Cond: (p_portfolio_hour_id = (ph.primary_key)::numeric)
17. 2.031 2.031 ↑ 1.0 1 677

Index Scan using temp_outage_equipment_idx on temp_outage_equipment oe (cost=0.28..8.30 rows=1 width=97) (actual time=0.003..0.003 rows=1 loops=677)

  • Index Cond: (((rdfid)::text = (l.te_rdfid)::text) AND ((((((to_char((pd.portfolio_day)::timestamp with time zone, 'MM-DD-YYYY'::text) || ' '::text) || lpad((ph.portfolio_hour_ending)::text, 2, '0'::text)) || ':00'::text))::timestamp without time zone) > outage_start) AND (((((((to_char((pd.portfolio_day)::timestamp with time zone, 'MM-DD-YYYY'::text) || ' '::text) || lpad((ph.portfolio_hour_ending)::text, 2, '0'::text)) || ':00'::text))::timestamp without time zone - '01:00:00'::interval)) < outage_end))
Planning time : 1.434 ms
Execution time : 2,541.586 ms