explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AoWy

Settings
# exclusive inclusive rows x rows loops node
1. 0.036 206.884 ↓ 22.0 44 1

Unique (cost=6,403.67..6,403.76 rows=2 width=832) (actual time=206.837..206.884 rows=44 loops=1)

2.          

CTE live_orgs

3. 0.044 0.113 ↑ 1.0 83 1

HashAggregate (cost=6.78..7.62 rows=84 width=4) (actual time=0.092..0.113 rows=83 loops=1)

  • Group Key: organisation_extended_funcs.organisation_id
4. 0.031 0.069 ↑ 1.0 83 1

Append (cost=0.27..6.57 rows=84 width=4) (actual time=0.010..0.069 rows=83 loops=1)

5. 0.036 0.036 ↑ 1.0 82 1

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs (cost=0.27..5.72 rows=83 width=4) (actual time=0.010..0.036 rows=82 loops=1)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 0
6. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

7.          

CTE gtab

8. 0.061 0.061 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.060..0.061 rows=1 loops=1)

9.          

CTE homogenized_geoms

10. 7.021 205.868 ↓ 215.0 215 1

Nested Loop (cost=4.85..6,395.15 rows=1 width=945) (actual time=1.680..205.868 rows=215 loops=1)

  • Join Filter: (e_cte.publisher_organisation_id = live_org.organisation_id)
  • Rows Removed by Join Filter: 17630
11. 0.468 194.547 ↓ 215.0 215 1

Nested Loop Left Join (cost=4.85..6,391.90 rows=1 width=394) (actual time=1.510..194.547 rows=215 loops=1)

12. 60.251 193.434 ↓ 215.0 215 1

Nested Loop Left Join (cost=0.57..6,383.59 rows=1 width=387) (actual time=1.500..193.434 rows=215 loops=1)

  • Join Filter: (e_cte.publisher_organisation_id = lku_orgref.organisation_id)
  • Rows Removed by Join Filter: 278855
13. 0.353 43.958 ↓ 215.0 215 1

Nested Loop Left Join (cost=0.57..6,326.99 rows=1 width=365) (actual time=0.823..43.958 rows=215 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (lku_ec_lang.nsa_code)::text)
14. 5.519 41.885 ↓ 215.0 215 1

Nested Loop Left Join (cost=0.57..6,323.87 rows=1 width=335) (actual time=0.807..41.885 rows=215 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (lku_ec.nsa_code)::text)
  • Rows Removed by Join Filter: 19350
15. 3.510 31.851 ↓ 215.0 215 1

Nested Loop (cost=0.57..6,319.82 rows=1 width=313) (actual time=0.782..31.851 rows=215 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
  • Rows Removed by Join Filter: 10719
16. 8.461 8.461 ↓ 994.0 994 1

Index Scan using idx_entity_type_coords_dates on entity e_cte (cost=0.57..6,316.42 rows=1 width=284) (actual time=0.130..8.461 rows=994 loops=1)

  • Index Cond: ((entity_type = ANY ('{2,4,5}'::integer[])) AND (location_point_easting >= 375000) AND (location_point_easting <= 475000) AND (location_point_northing >= 175000) AND (location_point_northing <= 275000) AND (start_date <= to_date('31/12/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_date('01/10/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
  • Filter: (active AND (COALESCE(tomtom__max_average_speed, '-1'::double precision) >= '-1'::double precision) AND (COALESCE((tm__sw_cancelled_status)::integer, 0) = 0))
  • Rows Removed by Filter: 548
17. 19.880 19.880 ↑ 1.0 11 994

Seq Scan on tm_nsa tmnsa (cost=0.00..3.26 rows=11 width=37) (actual time=0.003..0.020 rows=11 loops=994)

  • Filter: ((tm_nsa_type_id)::text = ANY ('{TMNSA21,TMNSA22,TMNSA23,TMNSA24,TMNSA01,TMNSA42,TMNSA41,TMNSA43,TMNSA46,TMNSA30,TMNSA32}'::text[]))
  • Rows Removed by Filter: 42
18. 4.515 4.515 ↑ 1.0 91 215

Seq Scan on entity_category lku_ec (cost=0.00..2.91 rows=91 width=29) (actual time=0.001..0.021 rows=91 loops=215)

19. 1.720 1.720 ↓ 0.0 0 215

Seq Scan on entity_category_lang lku_ec_lang (cost=0.00..3.11 rows=1 width=45) (actual time=0.008..0.008 rows=0 loops=215)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 89
20. 89.225 89.225 ↑ 1.0 1,298 215

Seq Scan on orgref lku_orgref (cost=0.00..40.38 rows=1,298 width=26) (actual time=0.001..0.415 rows=1,298 loops=215)

  • Filter: (active = 1)
  • Rows Removed by Filter: 12
21. 0.215 0.645 ↑ 1.0 1 215

Bitmap Heap Scan on la (cost=4.28..8.30 rows=1 width=15) (actual time=0.003..0.003 rows=1 loops=215)

  • Recheck Cond: (e_cte.lha_id = la_id)
  • Heap Blocks: exact=213
22. 0.430 0.430 ↑ 1.0 1 215

Bitmap Index Scan on la_pkey (cost=0.00..4.28 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=215)

  • Index Cond: (e_cte.lha_id = la_id)
23. 4.300 4.300 ↑ 1.0 83 215

CTE Scan on live_orgs live_org (cost=0.00..1.68 rows=84 width=4) (actual time=0.001..0.020 rows=83 loops=215)

24. 0.075 206.848 ↓ 22.0 44 1

Sort (cost=0.64..0.64 rows=2 width=832) (actual time=206.836..206.848 rows=44 loops=1)

  • Sort Key: d.id, d.theme_id, d.descriptor1, d.descriptor2, d.descriptor3, d.descriptor4, d.roadlocation, d.start_date, d.end_date, d.date_updated, d.swa_org_name_display, d.geom_type, d.layer_code, d.lat, d.lon, d.geojson_wgs84, d.tomtom__max_average_speed
  • Sort Method: quicksort Memory: 47kB
25. 0.030 206.773 ↓ 22.0 44 1

Append (cost=0.00..0.62 rows=2 width=832) (actual time=80.082..206.773 rows=44 loops=1)

26. 0.335 206.685 ↓ 44.0 44 1

Nested Loop (cost=0.00..0.30 rows=1 width=832) (actual time=80.081..206.685 rows=44 loops=1)

  • Join Filter: ((d.location_bng && gtab.bbox) AND _st_intersects(d.location_bng, gtab.bbox))
  • Rows Removed by Join Filter: 171
27. 0.065 0.065 ↑ 1.0 1 1

CTE Scan on gtab (cost=0.00..0.02 rows=1 width=32) (actual time=0.063..0.065 rows=1 loops=1)

28. 206.285 206.285 ↓ 215.0 215 1

CTE Scan on homogenized_geoms d (cost=0.00..0.02 rows=1 width=864) (actual time=1.684..206.285 rows=215 loops=1)

29. 0.001 0.058 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.30 rows=1 width=832) (actual time=0.058..0.058 rows=0 loops=1)

  • Join Filter: ((d_1.location_point_bng && gtab_1.bbox) AND _st_intersects(d_1.location_point_bng, gtab_1.bbox))
30. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on gtab gtab_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

31. 0.056 0.056 ↓ 0.0 0 1

CTE Scan on homogenized_geoms d_1 (cost=0.00..0.02 rows=1 width=864) (actual time=0.056..0.056 rows=0 loops=1)

  • Filter: (location_bng IS NULL)
  • Rows Removed by Filter: 215