explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WnrC

Settings
# exclusive inclusive rows x rows loops node
1. 207,296.034 207,296.034 ↓ 29.0 58 1

CTE Scan on geom_data gd (cost=3,424,905.34..3,424,905.38 rows=2 width=316) (actual time=207,295.933..207,296.034 rows=58 loops=1)

2.          

CTE live_orgs

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

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

CTE gtab

5. 0.064 0.064 ↑ 1.0 1 1

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

6.          

CTE raw_items

7. 9.671 207,224.884 ↓ 1,213.0 3,639 1

Nested Loop Left Join (cost=0.00..3,424,865.54 rows=3 width=3,702) (actual time=2.895..207,224.884 rows=3,639 loops=1)

  • Join Filter: (i_cte.item_id = sche.item_id)
  • Filter: (NOT COALESCE(sche.cancelled, false))
8. 207,215.213 207,215.213 ↓ 606.5 3,639 1

Seq Scan on item i_cte (cost=0.00..3,424,827.41 rows=6 width=3,700) (actual time=2.888..207,215.213 rows=3,639 loops=1)

  • Filter: (active AND (location_point_easting >= 393214) AND (location_point_easting <= 480509) AND (location_point_northing >= 245607) AND (location_point_northing <= 308006) 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 ((item_type)::text = ANY ('{RC,DR,HDR,O,BC}'::text[])))
  • Rows Removed by Filter: 24559774
9. 0.000 0.000 ↓ 0.0 0 3,639

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

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. 2.038 207,294.044 ↓ 635.0 635 1

Nested Loop Left Join (cost=27.54..33.08 rows=1 width=380) (actual time=207,288.527..207,294.044 rows=635 loops=1)

  • Join Filter: ((i_cte_1.item_type)::text = (itemtype1_lang.item_type)::text)
13. 0.330 207,288.831 ↓ 635.0 635 1

Hash Join (cost=27.54..31.34 rows=1 width=195) (actual time=207,288.494..207,288.831 rows=635 loops=1)

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

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

15. 0.412 207,288.474 ↓ 635.0 635 1

Hash (cost=27.53..27.53 rows=1 width=176) (actual time=207,288.474..207,288.474 rows=635 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 421kB
16. 0.384 207,288.062 ↓ 635.0 635 1

Hash Join (cost=25.55..27.53 rows=1 width=176) (actual time=207,287.659..207,288.062 rows=635 loops=1)

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

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

18. 1.217 207,287.587 ↓ 774.0 774 1

Hash (cost=25.54..25.54 rows=1 width=180) (actual time=207,287.587..207,287.587 rows=774 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 489kB
19. 4.941 207,286.370 ↓ 774.0 774 1

Nested Loop (cost=0.44..25.54 rows=1 width=180) (actual time=30.089..207,286.370 rows=774 loops=1)

20. 207,259.595 207,259.595 ↓ 1,213.0 3,639 1

CTE Scan on raw_items i_cte_1 (cost=0.00..0.06 rows=3 width=164) (actual time=2.901..207,259.595 rows=3,639 loops=1)

21. 21.834 21.834 ↓ 0.0 0 3,639

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=3,639)

  • 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. 3.175 3.175 ↓ 0.0 0 635

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

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

CTE geom_data

24. 0.042 207,295.989 ↓ 29.0 58 1

Unique (cost=0.64..0.73 rows=2 width=316) (actual time=207,295.931..207,295.989 rows=58 loops=1)

25. 0.103 207,295.947 ↓ 29.0 58 1

Sort (cost=0.64..0.64 rows=2 width=316) (actual time=207,295.930..207,295.947 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.026 207,295.844 ↓ 29.0 58 1

Append (cost=0.00..0.62 rows=2 width=316) (actual time=207,288.679..207,295.844 rows=58 loops=1)

27. 0.657 207,295.656 ↓ 58.0 58 1

Nested Loop (cost=0.00..0.30 rows=1 width=316) (actual time=207,288.678..207,295.656 rows=58 loops=1)

  • Join Filter: ((d.location_bng && gtab.bbox) AND _st_intersects(d.location_bng, gtab.bbox))
  • Rows Removed by Join Filter: 577
28. 0.068 0.068 ↑ 1.0 1 1

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

29. 207,294.931 207,294.931 ↓ 635.0 635 1

CTE Scan on homogenized_geoms d (cost=0.00..0.02 rows=1 width=348) (actual time=207,288.530..207,294.931 rows=635 loops=1)

30. 0.002 0.162 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.30 rows=1 width=316) (actual time=0.162..0.162 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.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.000..0.001 rows=1 loops=1)

32. 0.159 0.159 ↓ 0.0 0 1

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

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