explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TPhT

Settings
# exclusive inclusive rows x rows loops node
1. 0.484 50.511 ↓ 383.0 766 1

Hash Left Join (cost=18,722.82..18,722.93 rows=2 width=435) (actual time=49.949..50.511 rows=766 loops=1)

  • Hash Cond: (gd.tm_item_id = ef.item_id)
2.          

CTE live_orgs

3. 0.030 0.045 ↑ 1.0 104 1

Bitmap Heap Scan on organisation_extended_funcs (cost=5.10..11.44 rows=107 width=4) (actual time=0.022..0.045 rows=104 loops=1)

  • Recheck Cond: (extended_function_id = 14)
  • Heap Blocks: exact=5
4. 0.015 0.015 ↑ 1.0 104 1

Bitmap Index Scan on organisation_extended_funcs_pkey (cost=0.00..5.08 rows=107 width=0) (actual time=0.015..0.015 rows=104 loops=1)

  • Index Cond: (extended_function_id = 14)
5.          

CTE gtab

6. 0.068 0.068 ↑ 1.0 1 1

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

7.          

CTE tm_items

8. 1.192 25.700 ↓ 5.9 1,364 1

Hash Join (cost=5,485.34..18,650.35 rows=232 width=4,830) (actual time=10.240..25.700 rows=1,364 loops=1)

  • Hash Cond: (e_cte.publisher_organisation_id = live_org.organisation_id)
9. 1.191 24.419 ↓ 2.7 1,367 1

Nested Loop (cost=5,481.86..18,640.94 rows=498 width=4,836) (actual time=10.146..24.419 rows=1,367 loops=1)

10. 1.094 17.760 ↓ 2.7 1,367 1

Hash Left Join (cost=5,481.43..14,450.10 rows=498 width=4,815) (actual time=10.129..17.760 rows=1,367 loops=1)

  • Hash Cond: (i_cte.item_id = sche.item_id)
  • Filter: (NOT COALESCE(sche.cancelled, false))
11. 6.717 6.717 ↓ 1.4 1,367 1

Index Scan using idx_item_et_type_coords_datee on item i_cte (cost=0.56..7,798.76 rows=995 width=4,813) (actual time=0.050..6.717 rows=1,367 loops=1)

  • Index Cond: ((item_entity_type = 2) AND ((item_type)::text = ANY ('{RC,DC}'::text[])) AND (location_point_easting >= 385816) AND (location_point_easting <= 406245) AND (location_point_northing >= 235894) AND (location_point_northing <= 248403) AND (start_date <= (LOCALTIMESTAMP + '1 day'::interval)) AND (end_date <= LOCALTIMESTAMP))
  • Filter: active
  • Rows Removed by Filter: 133
12. 0.029 9.949 ↑ 347.4 94 1

Hash (cost=5,072.64..5,072.64 rows=32,658 width=11) (actual time=9.949..9.949 rows=94 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 261kB
13. 9.920 9.920 ↑ 347.4 94 1

Seq Scan on schedule sche (cost=0.00..5,072.64 rows=32,658 width=11) (actual time=0.162..9.920 rows=94 loops=1)

  • Filter: ((actual_start_date < CURRENT_TIMESTAMP) AND (actual_end_date > CURRENT_TIMESTAMP))
  • Rows Removed by Filter: 66750
14. 5.468 5.468 ↑ 1.0 1 1,367

Index Scan using pk_entity on entity e_cte (cost=0.43..8.42 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=1,367)

  • Index Cond: (entity_id = i_cte.entity_id)
15. 0.017 0.089 ↑ 1.0 104 1

Hash (cost=2.14..2.14 rows=107 width=4) (actual time=0.089..0.089 rows=104 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
16. 0.072 0.072 ↑ 1.0 104 1

CTE Scan on live_orgs live_org (cost=0.00..2.14 rows=107 width=4) (actual time=0.025..0.072 rows=104 loops=1)

17.          

CTE noms_items

18. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=4,830) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
19.          

CTE raw_items

20. 0.153 31.173 ↓ 5.9 1,364 1

Append (cost=0.00..4.64 rows=233 width=6,715) (actual time=10.250..31.173 rows=1,364 loops=1)

21. 31.019 31.019 ↓ 5.9 1,364 1

CTE Scan on tm_items (cost=0.00..4.64 rows=232 width=6,715) (actual time=10.250..31.019 rows=1,364 loops=1)

22. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on noms_items (cost=0.00..0.00 rows=1 width=6,715) (actual time=0.001..0.001 rows=0 loops=1)

23.          

CTE homogenized_geoms

24. 0.175 41.804 ↓ 895.0 895 1

Result (cost=9.34..16.55 rows=1 width=381) (actual time=35.839..41.804 rows=895 loops=1)

  • One-Time Filter: ((COALESCE(CURRENT_DATE, CURRENT_DATE) - '3650 days'::interval day) <= CURRENT_DATE)
25. 0.963 41.629 ↓ 895.0 895 1

Nested Loop Left Join (cost=9.34..16.55 rows=1 width=381) (actual time=35.836..41.629 rows=895 loops=1)

  • Join Filter: ((i.item_type)::text = (itemtype1_lang.item_type)::text)
26. 0.379 36.191 ↓ 895.0 895 1

Hash Join (cost=9.33..13.43 rows=1 width=226) (actual time=35.819..36.191 rows=895 loops=1)

  • Hash Cond: ((itemtype1.item_type)::text = (i.item_type)::text)
27. 0.014 0.014 ↑ 1.0 79 1

Seq Scan on item_type itemtype1 (cost=0.00..3.79 rows=79 width=28) (actual time=0.005..0.014 rows=79 loops=1)

28. 0.776 35.798 ↓ 895.0 895 1

Hash (cost=9.32..9.32 rows=1 width=201) (actual time=35.798..35.798 rows=895 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 478kB
29. 35.022 35.022 ↓ 895.0 895 1

CTE Scan on raw_items i (cost=0.00..9.32 rows=1 width=201) (actual time=10.352..35.022 rows=895 loops=1)

  • Filter: (e_active AND (tm__sw_cancelled_status = 0) AND (published = ANY (('{1,0}'::text[])::integer[])))
  • Rows Removed by Filter: 469
30. 4.475 4.475 ↓ 0.0 0 895

Seq Scan on item_type_lang itemtype1_lang (cost=0.00..3.08 rows=1 width=434) (actual time=0.005..0.005 rows=0 loops=895)

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

CTE geom_data

32. 0.104 45.754 ↓ 383.0 766 1

Append (cost=0.00..0.62 rows=2 width=341) (actual time=36.303..45.754 rows=766 loops=1)

33. 2.409 45.457 ↓ 766.0 766 1

Nested Loop (cost=0.00..0.30 rows=1 width=341) (actual time=36.302..45.457 rows=766 loops=1)

  • Join Filter: ((d.location_bng && gtab.bbox) AND _st_intersects(d.location_bng, gtab.bbox))
  • Rows Removed by Join Filter: 129
34. 0.069 0.069 ↑ 1.0 1 1

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

35. 42.979 42.979 ↓ 895.0 895 1

CTE Scan on homogenized_geoms d (cost=0.00..0.02 rows=1 width=373) (actual time=35.843..42.979 rows=895 loops=1)

36. 0.000 0.193 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.30 rows=1 width=341) (actual time=0.193..0.193 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))
37. 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)

38. 0.192 0.192 ↓ 0.0 0 1

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

  • Filter: (location_bng IS NULL)
  • Rows Removed by Filter: 895
39.          

CTE today

40. 0.017 0.017 ↑ 1.0 1 1

Result (cost=0.00..0.25 rows=1 width=60) (actual time=0.016..0.017 rows=1 loops=1)

41.          

CTE raw_efs

42. 0.070 9.142 ↑ 1.0 2 1

Nested Loop (cost=0.29..19.95 rows=2 width=202) (actual time=8.795..9.142 rows=2 loops=1)

43. 0.018 0.018 ↑ 1.0 1 1

CTE Scan on today t (cost=0.00..0.02 rows=1 width=56) (actual time=0.018..0.018 rows=1 loops=1)

44. 0.155 9.054 ↑ 1.0 2 1

Nested Loop (cost=0.29..16.68 rows=2 width=82) (actual time=8.754..9.054 rows=2 loops=1)

45. 7.367 7.367 ↓ 383.0 766 1

CTE Scan on geom_data i_1 (cost=0.00..0.04 rows=2 width=16) (actual time=0.000..7.367 rows=766 loops=1)

46. 1.532 1.532 ↓ 0.0 0 766

Index Scan using idx_enforcement_pattern_item_id on enforcement_pattern ef_1 (cost=0.29..8.31 rows=1 width=74) (actual time=0.002..0.002 rows=0 loops=766)

  • Index Cond: (item_id = i_1.tm_item_id)
47.          

CTE schedules

48. 0.006 13.602 ↑ 1.0 2 1

Nested Loop Left Join (cost=0.71..18.62 rows=2 width=93) (actual time=13.251..13.602 rows=2 loops=1)

  • Join Filter: ((efs.enforcement_pattern_id = s.enforcement_pattern_id) AND (efs.actual_start_date = s.start_date) AND (efs.actual_end_date = s.end_date))
49. 9.148 9.148 ↑ 1.0 2 1

CTE Scan on raw_efs efs (cost=0.00..0.04 rows=2 width=93) (actual time=8.799..9.148 rows=2 loops=1)

50. 0.001 4.448 ↓ 0.0 0 2

Materialize (cost=0.71..18.46 rows=3 width=36) (actual time=2.224..2.224 rows=0 loops=2)

51. 0.002 4.447 ↓ 0.0 0 1

Nested Loop (cost=0.71..18.44 rows=3 width=36) (actual time=4.447..4.447 rows=0 loops=1)

52. 0.575 4.435 ↑ 1.0 2 1

Nested Loop (cost=0.29..16.68 rows=2 width=4) (actual time=0.959..4.435 rows=2 loops=1)

53. 3.094 3.094 ↓ 383.0 766 1

CTE Scan on geom_data i_2 (cost=0.00..0.04 rows=2 width=8) (actual time=0.000..3.094 rows=766 loops=1)

54. 0.766 0.766 ↓ 0.0 0 766

Index Scan using idx_enforcement_pattern_item_id on enforcement_pattern ef_2 (cost=0.29..8.31 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=766)

  • Index Cond: (item_id = i_2.tm_item_id)
55. 0.010 0.010 ↓ 0.0 0 2

Index Scan using idx_enforcement_pattern_id on schedule s (cost=0.42..0.84 rows=4 width=36) (actual time=0.005..0.005 rows=0 loops=2)

  • Index Cond: (enforcement_pattern_id = ef_2.enforcement_pattern_id)
  • Filter: ((NOT cancelled) AND (actual_end_date > CURRENT_TIMESTAMP))
56.          

CTE inforce_or_next_ef

57. 0.001 13.628 ↓ 2.0 2 1

Subquery Scan on ef_3 (cost=0.06..0.10 rows=1 width=101) (actual time=13.625..13.628 rows=2 loops=1)

  • Filter: (ef_3.rn = 1)
58. 0.013 13.627 ↓ 2.0 2 1

WindowAgg (cost=0.06..0.08 rows=1 width=101) (actual time=13.624..13.627 rows=2 loops=1)

59. 0.008 13.614 ↓ 2.0 2 1

Sort (cost=0.06..0.07 rows=1 width=93) (actual time=13.614..13.614 rows=2 loops=1)

  • Sort Key: efs_1.item_id, efs_1.actual_end_date
  • Sort Method: quicksort Memory: 25kB
60. 13.606 13.606 ↓ 2.0 2 1

CTE Scan on schedules efs_1 (cost=0.00..0.05 rows=1 width=93) (actual time=13.253..13.606 rows=2 loops=1)

  • Filter: (actual_end_date > CURRENT_TIMESTAMP)
61. 36.392 36.392 ↓ 383.0 766 1

CTE Scan on geom_data gd (cost=0.00..0.04 rows=2 width=341) (actual time=36.305..36.392 rows=766 loops=1)

62. 0.004 13.635 ↓ 2.0 2 1

Hash (cost=0.02..0.02 rows=1 width=53) (actual time=13.635..13.635 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
63. 13.631 13.631 ↓ 2.0 2 1

CTE Scan on inforce_or_next_ef ef (cost=0.00..0.02 rows=1 width=53) (actual time=13.627..13.631 rows=2 loops=1)

Planning time : 6.452 ms
Execution time : 52.458 ms