explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iMw3

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 90.853 ↓ 0.0 0 1

Nested Loop Left Join (cost=6.29..121,129.03 rows=4 width=531) (actual time=90.853..90.853 rows=0 loops=1)

  • Join Filter: (d.publisher_organisation_id = liveorg.organisation_id)
2.          

CTE live_orgs

3. 0.000 0.000 ↓ 0.0 0

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs (cost=0.27..5.72 rows=83 width=4) (never executed)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 0
4. 0.001 90.853 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.57..121,102.92 rows=4 width=395) (actual time=90.853..90.853 rows=0 loops=1)

  • Join Filter: ((ec.layer_code)::text = (tn.layer_code)::text)
5. 0.001 90.852 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.57..121,097.08 rows=4 width=387) (actual time=90.852..90.852 rows=0 loops=1)

  • Join Filter: ((d.entity_category)::text = (ec_lang.nsa_code)::text)
6. 0.863 90.851 ↓ 0.0 0 1

Nested Loop (cost=0.57..121,093.91 rows=4 width=349) (actual time=90.851..90.851 rows=0 loops=1)

  • Join Filter: (d.responsible_org_orgref = resporgref.swa_org_ref)
7. 0.426 0.426 ↑ 1.0 1,298 1

Seq Scan on orgref resporgref (cost=0.00..40.38 rows=1,298 width=26) (actual time=0.009..0.426 rows=1,298 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 12
8. 0.000 89.562 ↓ 0.0 0 1,298

Materialize (cost=0.57..120,975.66 rows=4 width=327) (actual time=0.069..0.069 rows=0 loops=1,298)

9. 0.918 89.646 ↓ 0.0 0 1

Nested Loop (cost=0.57..120,975.64 rows=4 width=327) (actual time=89.646..89.646 rows=0 loops=1)

  • Join Filter: (d.publisher_orgref = puborgref.swa_org_ref)
10. 0.464 0.464 ↑ 1.0 1,298 1

Seq Scan on orgref puborgref (cost=0.00..40.38 rows=1,298 width=26) (actual time=0.002..0.464 rows=1,298 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 12
11. 0.000 88.264 ↓ 0.0 0 1,298

Materialize (cost=0.57..120,857.40 rows=4 width=309) (actual time=0.068..0.068 rows=0 loops=1,298)

12. 0.008 88.402 ↓ 0.0 0 1

Nested Loop (cost=0.57..120,857.38 rows=4 width=309) (actual time=88.402..88.402 rows=0 loops=1)

  • Join Filter: ((d.entity_category)::text = (ec.nsa_code)::text)
13. 0.026 0.026 ↓ 1.0 24 1

Seq Scan on entity_category ec (cost=0.00..3.14 rows=23 width=55) (actual time=0.006..0.026 rows=24 loops=1)

  • Filter: ((entitygroupid)::text = 'INC'::text)
  • Rows Removed by Filter: 67
14. 0.011 88.368 ↓ 0.0 0 24

Materialize (cost=0.57..120,849.44 rows=14 width=261) (actual time=3.682..3.682 rows=0 loops=24)

15. 88.357 88.357 ↓ 0.0 0 1

Index Scan using idx_entity_type_coords_dates on entity d (cost=0.57..120,849.37 rows=14 width=261) (actual time=88.357..88.357 rows=0 loops=1)

  • Index Cond: ((entity_type > 1) AND (location_point_easting >= 400000) AND (location_point_easting <= 450000) AND (location_point_northing >= 200000) AND (location_point_northing <= 250000) AND (start_date <= to_timestamp('21/12/2018 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('01/09/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
  • Filter: ((tm__entity_id_sw_xref IS NULL) AND (COALESCE(tomtom__max_average_speed, '-9'::double precision) >= '0'::double precision) AND (published = 1) AND ((entity_category)::text = ANY ('{TMNSA08,INCIDENT,QUEUE,OBSTRUCTION,NSA022,ALERT,"SIGNAL FAILURE",TMNSA00,ACCIDENT,NSA021,CLOSED,TMNSA10,TMNSA11,TMNSA07,WEATHER,NSA023,TMNSA12,TMNSA36,TMNSA37,TMNSA37}'::text[])))
  • Rows Removed by Filter: 736
16. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..3.12 rows=1 width=45) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Seq Scan on entity_category_lang ec_lang (cost=0.00..3.11 rows=1 width=45) (never executed)

  • Filter: ((code)::text = 'en'::text)
18. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..2.80 rows=53 width=32) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Seq Scan on tm_nsa tn (cost=0.00..2.53 rows=53 width=32) (never executed)

20. 0.000 0.000 ↓ 0.0 0

CTE Scan on live_orgs liveorg (cost=0.00..1.66 rows=83 width=4) (never executed)