explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n6Ty

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.010 12.390 ↓ 0.0 0 1

Sort (cost=3,521.71..3,521.72 rows=1 width=706) (actual time=12.390..12.390 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 12.380 ↓ 0.0 0 1

Subquery Scan on outages (cost=334.66..3,521.70 rows=1 width=706) (actual time=12.380..12.380 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 12.380 ↓ 0.0 0 1

Append (cost=334.66..3,521.57 rows=3 width=702) (actual time=12.380..12.380 rows=0 loops=1)

4. 10.542 12.337 ↓ 0.0 0 1

Bitmap Heap Scan on outages_v2 (cost=334.66..3,466.27 rows=1 width=256) (actual time=12.337..12.337 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.000 1.795 ↓ 0.0 0 1

BitmapOr (cost=334.66..334.66 rows=16,769 width=0) (actual time=1.795..1.795 rows=0 loops=1)

6. 0.011 0.011 ↓ 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.011..0.011 rows=0 loops=1)

  • Index Cond: ((end_datetime >= (now() - '01:00:00'::interval)) AND (end_datetime <= now()))
7. 1.781 1.781 ↓ 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.781..1.781 rows=17,555 loops=1)

  • Index Cond: (end_datetime IS NULL)
8. 0.003 0.003 ↑ 103.0 1 1

Bitmap Index Scan on outages_v2_end_datetime_index (cost=0.00..5.07 rows=103 width=0) (actual time=0.003..0.003 rows=1 loops=1)

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

Subquery Scan on *SELECT* 2 (cost=4.52..43.17 rows=1 width=566) (actual time=0.027..0.027 rows=0 loops=1)

10. 0.007 0.027 ↓ 0.0 0 1

Bitmap Heap Scan on transformer_outages (cost=4.52..43.16 rows=1 width=364) (actual time=0.027..0.027 rows=0 loops=1)

  • Recheck 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
  • Heap Blocks: exact=2
11. 0.020 0.020 ↑ 5.0 2 1

Bitmap Index Scan on transformer_outages_transformer_name_idx (cost=0.00..4.52 rows=10 width=0) (actual time=0.020..0.020 rows=2 loops=1)

  • Index Cond: ((transformer_name IS NOT NULL) AND ((transformer_name)::text = 'C-1494'::text))
12. 0.000 0.015 ↓ 0.0 0 1

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

13. 0.015 0.015 ↓ 0.0 0 1

Index Scan using plannedoutages_transformer_name_idx on plannedoutages (cost=0.29..12.12 rows=1 width=536) (actual time=0.015..0.015 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.930 ms
Execution time : 12.578 ms