explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HBA9 : Optimization for: plan #n6Ty

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.008 19.384 ↓ 0.0 0 1

Sort (cost=11,745.96..11,745.97 rows=1 width=706) (actual time=19.384..19.384 rows=0 loops=1)

  • Sort Key: (CASE WHEN (outages.event_type = 'Planned'::text) THEN 1 ELSE 2 END), outages.type_code DESC, outages.start_datetime DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.000 19.376 ↓ 0.0 0 1

Subquery Scan on outages (cost=334.65..11,745.95 rows=1 width=706) (actual time=19.376..19.376 rows=0 loops=1)

  • Filter: (((lower(outages.status) <> 'other'::text) OR (outages.status IS NULL)) AND ((lower(outages.status) <> 'resolved'::text) OR (outages.status IS NULL) OR (((lower(outages.status) = 'resolved'::text) OR (lower(outages.status) = 'other'::text)) AND (outages.end_datetime IS NOT NULL) AND (outages.end_datetime >= (now() - '05:00:00'::interval)) AND (outages.end_datetime <= now()))))
3. 0.001 19.376 ↓ 0.0 0 1

Append (cost=334.65..11,745.82 rows=3 width=702) (actual time=19.376..19.376 rows=0 loops=1)

4. 10.398 12.204 ↓ 0.0 0 1

Bitmap Heap Scan on outages_v2 (cost=334.65..3,466.21 rows=1 width=256) (actual time=12.204..12.204 rows=0 loops=1)

  • Recheck Cond: (((end_datetime >= (now() - '01:00:00'::interval)) AND (end_datetime <= now())) OR (end_datetime IS NULL) OR (end_datetime > now()))
  • Filter: ((fault_type <> 'FEEDER'::text) AND (((tui_id = '1002890936'::text) AND (tui_id IS NOT NULL) AND (fault_type = 'INDIVIDUAL'::text)) OR ((icp_number = '0261954792LCBD7'::text) AND (icp_number IS NOT NULL) AND (fault_type = 'INDIVIDUAL'::text)) OR (((transformer_name)::text = 'C-1494'::text) AND (transformer_name IS NOT NULL) AND ((fault_type IS NULL) OR (fault_type <> 'INDIVIDUAL'::text)))))
  • Rows Removed by Filter: 16,336
  • Heap Blocks: exact=2,323
5. 0.001 1.806 ↓ 0.0 0 1

BitmapOr (cost=334.65..334.65 rows=16,768 width=0) (actual time=1.806..1.806 rows=0 loops=1)

6. 0.009 0.009 ↓ 0.0 0 1

Bitmap Index Scan on outages_v2_end_datetime_index (cost=0.00..4.32 rows=2 width=0) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: ((end_datetime >= (now() - '01:00:00'::interval)) AND (end_datetime <= now()))
7. 1.786 1.786 ↓ 1.1 17,555 1

Bitmap Index Scan on outages_v2_end_datetime_index (cost=0.00..325.27 rows=16,664 width=0) (actual time=1.786..1.786 rows=17,555 loops=1)

  • Index Cond: (end_datetime IS NULL)
8. 0.010 0.010 ↑ 102.0 1 1

Bitmap Index Scan on outages_v2_end_datetime_index (cost=0.00..5.06 rows=102 width=0) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (end_datetime > now())
9. 0.000 6.623 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.42..7,679.81 rows=1 width=566) (actual time=6.623..6.623 rows=0 loops=1)

10. 6.623 6.623 ↓ 0.0 0 1

Index Scan using transformer_outages_pk on transformer_outages (cost=0.42..7,679.80 rows=1 width=364) (actual time=6.623..6.623 rows=0 loops=1)

  • Index Cond: ((transformer_name IS NOT NULL) AND ((transformer_name)::text = 'C-1494'::text))
  • Filter: ((event_type_name = 'Unplanned'::text) AND (((restoration_date_time >= (now() - '01:00:00'::interval)) AND (restoration_date_time <= now())) OR (restoration_date_time IS NULL) OR (restoration_date_time > now())))
  • Rows Removed by Filter: 2
11. 0.002 0.548 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=0.29..599.79 rows=1 width=589) (actual time=0.548..0.548 rows=0 loops=1)

12. 0.546 0.546 ↓ 0.0 0 1

Index Scan using plannedoutages_pk on plannedoutages (cost=0.29..599.78 rows=1 width=536) (actual time=0.546..0.546 rows=0 loops=1)

  • Index Cond: ((transformer_name IS NOT NULL) AND ((transformer_name)::text = 'C-1494'::text))
  • Filter: (((outage_status)::text <> 'Cancelled'::text) AND (advertised_start_date_time < now()) AND (advertised_restoration_date_time > (now() - '01:00:00'::interval)) AND (((advertised_restoration_date_time >= (now() - '01:00:00'::interval)) AND (advertised_restoration_date_time <= now())) OR (advertised_restoration_date_time IS NULL) OR (advertised_restoration_date_time > now())))
  • Rows Removed by Filter: 1
Planning time : 0.853 ms
Execution time : 19.570 ms