explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZunB0

Settings
# exclusive inclusive rows x rows loops node
1. 332.301 332.301 ↓ 29.0 58 1

CTE Scan on geom_data gd (cost=237,457.57..237,457.61 rows=2 width=316) (actual time=332.114..332.301 rows=58 loops=1)

2.          

CTE live_orgs

3. 0.059 0.059 ↑ 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.015..0.059 rows=82 loops=1)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 0
4.          

CTE gtab

5. 0.062 0.062 ↑ 1.0 1 1

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

6.          

CTE raw_items

7. 2.990 312.115 ↓ 291.0 1,164 1

Nested Loop Left Join (cost=0.57..237,409.26 rows=4 width=3,147) (actual time=0.493..312.115 rows=1,164 loops=1)

  • Join Filter: (i_cte.item_id = sche.item_id)
  • Filter: (NOT COALESCE(sche.cancelled, false))
8. 309.125 309.125 ↓ 145.5 1,164 1

Index Scan using idx_item_coords_dates on item i_cte (cost=0.57..237,368.04 rows=8 width=3,145) (actual time=0.486..309.125 rows=1,164 loops=1)

  • Index Cond: ((location_point_easting >= 408214) AND (location_point_easting <= 465509) AND (location_point_northing >= 260607) AND (location_point_northing <= 293006) AND (start_date >= to_timestamp('09/08/2018 23:39:39'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('09/08/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (active = true))
  • Filter: (active AND ((item_type)::text = ANY ('{RC,DR,HDR,O,BC}'::text[])))
  • Rows Removed by Filter: 3170
9. 0.000 0.000 ↓ 0.0 0 1,164

Materialize (cost=0.00..29.12 rows=103 width=11) (actual time=0.000..0.000 rows=0 loops=1,164)

10. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on schedule sche (cost=0.00..28.60 rows=103 width=11) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((actual_start_date < to_timestamp('09/08/2018 23:39:39'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (actual_end_date > to_timestamp('09/08/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
11.          

CTE homogenized_geoms

12. 1.252 330.516 ↓ 233.0 233 1

Nested Loop Left Join (cost=36.06..41.59 rows=1 width=380) (actual time=326.460..330.516 rows=233 loops=1)

  • Join Filter: ((i_cte_1.item_type)::text = (itemtype1_lang.item_type)::text)
13. 0.285 326.701 ↓ 233.0 233 1

Hash Join (cost=36.06..39.85 rows=1 width=195) (actual time=326.414..326.701 rows=233 loops=1)

  • Hash Cond: ((itemtype1.item_type)::text = (i_cte_1.item_type)::text)
14. 0.030 0.030 ↑ 1.0 57 1

Seq Scan on item_type itemtype1 (cost=0.00..3.57 rows=57 width=22) (actual time=0.006..0.030 rows=57 loops=1)

15. 0.267 326.386 ↓ 233.0 233 1

Hash (cost=36.04..36.04 rows=1 width=176) (actual time=326.386..326.386 rows=233 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 148kB
16. 0.248 326.119 ↓ 233.0 233 1

Hash Join (cost=34.06..36.04 rows=1 width=176) (actual time=325.831..326.119 rows=233 loops=1)

  • Hash Cond: (live_org.organisation_id = e_cte.publisher_organisation_id)
17. 0.140 0.140 ↑ 1.0 82 1

CTE Scan on live_orgs live_org (cost=0.00..1.66 rows=83 width=4) (actual time=0.017..0.140 rows=82 loops=1)

18. 0.352 325.731 ↓ 233.0 233 1

Hash (cost=34.05..34.05 rows=1 width=180) (actual time=325.731..325.731 rows=233 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 149kB
19. 0.905 325.379 ↓ 233.0 233 1

Nested Loop (cost=0.44..34.05 rows=1 width=180) (actual time=3.247..325.379 rows=233 loops=1)

20. 317.490 317.490 ↓ 291.0 1,164 1

CTE Scan on raw_items i_cte_1 (cost=0.00..0.08 rows=4 width=164) (actual time=0.499..317.490 rows=1,164 loops=1)

21. 6.984 6.984 ↓ 0.0 0 1,164

Index Scan using idx_entity_id on entity e_cte (cost=0.44..8.48 rows=1 width=24) (actual time=0.006..0.006 rows=0 loops=1,164)

  • Index Cond: (entity_id = i_cte_1.entity_id)
  • Filter: (active AND (COALESCE(tomtom__max_impact, '-9'::integer) >= 0) AND (entity_type = 2) AND (COALESCE((tm__sw_cancelled_status)::integer, 0) = 0) AND (published = ANY (('{1,0}'::text[])::integer[])))
  • Rows Removed by Filter: 1
22. 2.563 2.563 ↓ 0.0 0 233

Seq Scan on item_type_lang itemtype1_lang (cost=0.00..1.71 rows=1 width=37) (actual time=0.011..0.011 rows=0 loops=233)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 57
23.          

CTE geom_data

24. 0.082 332.219 ↓ 29.0 58 1

Unique (cost=0.64..0.73 rows=2 width=316) (actual time=332.112..332.219 rows=58 loops=1)

25. 0.126 332.137 ↓ 29.0 58 1

Sort (cost=0.64..0.64 rows=2 width=316) (actual time=332.110..332.137 rows=58 loops=1)

  • Sort Key: d.id, d.linked_id, d.theme_id, d.descriptor1, d.descriptor2, d.descriptor3, d.descriptor4, d.tm_entity_id, d.tm_item_id, d.tm_item_type, d.start_date, d.end_date, d.geojson_wgs84, d.lon, d.lat, d.point_geojson_wgs84, d.geom_type, d.tomtom__max_average_speed
  • Sort Method: quicksort Memory: 44kB
26. 0.047 332.011 ↓ 29.0 58 1

Append (cost=0.00..0.62 rows=2 width=316) (actual time=326.876..332.011 rows=58 loops=1)

27. 0.716 331.848 ↓ 58.0 58 1

Nested Loop (cost=0.00..0.30 rows=1 width=316) (actual time=326.875..331.848 rows=58 loops=1)

  • Join Filter: ((d.location_bng && gtab.bbox) AND _st_intersects(d.location_bng, gtab.bbox))
  • Rows Removed by Join Filter: 175
28. 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)

29. 331.067 331.067 ↓ 233.0 233 1

CTE Scan on homogenized_geoms d (cost=0.00..0.02 rows=1 width=348) (actual time=326.465..331.067 rows=233 loops=1)

30. 0.002 0.116 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.30 rows=1 width=316) (actual time=0.116..0.116 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))
31. 0.003 0.003 ↑ 1.0 1 1

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

32. 0.111 0.111 ↓ 0.0 0 1

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

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