explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Sml

Settings
# exclusive inclusive rows x rows loops node
1. 0.175 13.318 ↓ 2.0 2 1

Nested Loop (cost=3.74..215.10 rows=1 width=487) (actual time=2.282..13.318 rows=2 loops=1)

  • Join Filter: (""overlaps""(x.contact_start_utc, x.contact_stop_utc, pdv.created_utc, pdv.completed_utc) AND (s.customer_id = pdv.customer_id))
  • Rows Removed by Join Filter: 448
2. 0.292 12.650 ↓ 17.0 17 1

Nested Loop (cost=3.32..214.24 rows=1 width=478) (actual time=2.106..12.650 rows=17 loops=1)

  • Join Filter: ((s.customer_id = t.customer_id) AND ((s.rtls_vendor_id)::text = (t.rtls_vendor_id)::text))
3. 0.253 7.543 ↓ 321.0 321 1

Nested Loop (cost=3.04..207.99 rows=1 width=753) (actual time=2.083..7.543 rows=321 loops=1)

4. 3.186 6.648 ↓ 321.0 321 1

Hash Join (cost=2.75..207.55 rows=1 width=730) (actual time=2.064..6.648 rows=321 loops=1)

  • Hash Cond: ((s.customer_id = x.customer_id) AND ((s.rtls_vendor_id)::text = (x.rtls_vendor_id)::text) AND ((s.sensor_value)::text = (x.sensor_value)::text))
5. 1.431 1.431 ↑ 1.0 5,158 1

Seq Scan on sensor s (cost=0.00..146.76 rows=5,158 width=14) (actual time=0.012..1.431 rows=5,158 loops=1)

  • Filter: (deleted_utc IS NULL)
  • Rows Removed by Filter: 18
6. 0.155 2.031 ↓ 3.2 321 1

Hash (cost=1.00..1.00 rows=100 width=940) (actual time=2.031..2.031 rows=321 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
7. 1.876 1.876 ↓ 3.2 321 1

Function Scan on json_to_recordset x (cost=0.00..1.00 rows=100 width=940) (actual time=1.780..1.876 rows=321 loops=1)

8. 0.642 0.642 ↑ 1.0 1 321

Index Scan using location_pk on location l (cost=0.28..0.43 rows=1 width=31) (actual time=0.001..0.002 rows=1 loops=321)

  • Index Cond: ((customer_id = s.customer_id) AND (id = s.location_id))
  • Filter: (deleted_utc IS NULL)
9. 4.815 4.815 ↓ 0.0 0 321

Index Scan using idx_tag_contacttrace on tag t (cost=0.29..6.23 rows=1 width=15) (actual time=0.015..0.015 rows=0 loops=321)

  • Index Cond: ((customer_id = x.customer_id) AND ((rtls_vendor_id)::text = (x.rtls_vendor_id)::text) AND ((tag_value)::text = (x.contact_tag_value)::text))
10. 0.493 0.493 ↓ 8.7 26 17

Index Scan using idx_patient_department_visit_contacttrace on patient_department_visit pdv (cost=0.41..0.82 rows=3 width=51) (actual time=0.003..0.029 rows=26 loops=17)

  • Index Cond: ((customer_id = t.customer_id) AND (tag_id = t.id))
  • Filter: (deleted_utc IS NULL)
  • Rows Removed by Filter: 0
Planning time : 2.571 ms
Execution time : 13.467 ms