explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gu34

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 714.220 ↓ 0.0 0 1

Nested Loop Left Join (cost=6.29..1,014,185.13 rows=31 width=531) (actual time=714.220..714.220 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.000 714.218 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.57..1,014,027.06 rows=31 width=395) (actual time=714.218..714.218 rows=0 loops=1)

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

Nested Loop Left Join (cost=0.57..1,013,999.75 rows=31 width=387) (actual time=714.218..714.218 rows=0 loops=1)

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

Nested Loop (cost=0.57..1,013,996.17 rows=31 width=349) (actual time=714.217..714.217 rows=0 loops=1)

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

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

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

Materialize (cost=0.57..1,013,352.31 rows=31 width=327) (actual time=0.550..0.550 rows=0 loops=1,298)

9. 0.000 713.018 ↓ 0.0 0 1

Nested Loop (cost=0.57..1,013,352.15 rows=31 width=327) (actual time=713.018..713.018 rows=0 loops=1)

  • Join Filter: (d.publisher_orgref = puborgref.swa_org_ref)
10. 0.507 0.507 ↑ 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.507 rows=1,298 loops=1)

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

Materialize (cost=0.57..1,012,708.28 rows=31 width=309) (actual time=0.549..0.549 rows=0 loops=1,298)

12. 0.002 711.764 ↓ 0.0 0 1

Nested Loop (cost=0.57..1,012,708.13 rows=31 width=309) (actual time=711.764..711.764 rows=0 loops=1)

  • Join Filter: ((d.entity_category)::text = (ec.nsa_code)::text)
13. 711.762 711.762 ↓ 0.0 0 1

Index Scan using idx_entity_type_coords_dates on entity d (cost=0.57..1,012,662.50 rows=123 width=261) (actual time=711.762..711.762 rows=0 loops=1)

  • Index Cond: ((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: 6872
14. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..3.25 rows=23 width=55) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Seq Scan on entity_category ec (cost=0.00..3.14 rows=23 width=55) (never executed)

  • Filter: ((entitygroupid)::text = 'INC'::text)
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)