explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QEUB

Settings
# exclusive inclusive rows x rows loops node
1. 0.549 58.920 ↓ 384.0 768 1

Hash Left Join (cost=9,542.71..9,542.83 rows=2 width=435) (actual time=58.284..58.920 rows=768 loops=1)

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

CTE live_orgs

3. 0.040 0.061 ↑ 1.0 104 1

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

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

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

  • Index Cond: (extended_function_id = 14)
5.          

CTE gtab

6. 0.096 0.096 ↑ 1.0 1 1

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

7.          

CTE tm_items

8. 2.784 26.165 ↓ 10.7 1,367 1

Nested Loop Left Join (cost=8.89..9,476.11 rows=128 width=4,560) (actual time=0.251..26.165 rows=1,367 loops=1)

  • Filter: (sche.cancelled IS DISTINCT FROM true)
9. 1.458 20.647 ↓ 10.7 1,367 1

Hash Join (cost=8.47..6,696.67 rows=128 width=4,560) (actual time=0.236..20.647 rows=1,367 loops=1)

  • Hash Cond: (e_cte.publisher_organisation_id = live_org.organisation_id)
10. 1.990 19.058 ↓ 5.0 1,370 1

Nested Loop (cost=4.99..6,690.89 rows=275 width=4,564) (actual time=0.098..19.058 rows=1,370 loops=1)

11. 8.848 8.848 ↓ 5.0 1,370 1

Index Scan using idx_item_et_type_coords_datee on item i_cte (cost=0.56..4,368.44 rows=275 width=4,543) (actual time=0.074..8.848 rows=1,370 loops=1)

  • Index Cond: ((item_entity_type = 2) AND ((item_type)::text = ANY ('{RC,DC}'::text[])) AND (location_point_easting >= 385,816) AND (location_point_easting <= 406,245) AND (location_point_northing >= 235,894) AND (location_point_northing <= 248,403) AND (start_date <= (LOCALTIMESTAMP + '1 day'::interval)) AND (end_date <= LOCALTIMESTAMP))
  • Filter: active
  • Rows Removed by Filter: 133
12. 4.110 8.220 ↑ 1.0 1 1,370

Bitmap Heap Scan on entity e_cte (cost=4.43..8.45 rows=1 width=29) (actual time=0.006..0.006 rows=1 loops=1,370)

  • Recheck Cond: (entity_id = i_cte.entity_id)
  • Heap Blocks: exact=1,371
13. 4.110 4.110 ↑ 1.0 1 1,370

Bitmap Index Scan on pk_entity (cost=0.00..4.43 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1,370)

  • Index Cond: (entity_id = i_cte.entity_id)
14. 0.025 0.131 ↑ 1.0 104 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
15. 0.106 0.106 ↑ 1.0 104 1

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

16. 2.734 2.734 ↓ 0.0 0 1,367

Index Scan using idx_schedule_item_id on schedule sche (cost=0.42..21.58 rows=10 width=11) (actual time=0.002..0.002 rows=0 loops=1,367)

  • Index Cond: (i_cte.item_id = item_id)
  • Filter: ((actual_start_date < CURRENT_TIMESTAMP) AND (actual_end_date > CURRENT_TIMESTAMP))
17.          

CTE noms_items

18. 0.001 0.001 ↓ 0.0 0 1

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

  • One-Time Filter: false
19.          

CTE raw_items

20. 0.254 33.117 ↓ 10.6 1,367 1

Append (cost=0.00..2.56 rows=129 width=6,715) (actual time=0.265..33.117 rows=1,367 loops=1)

21. 32.862 32.862 ↓ 10.7 1,367 1

CTE Scan on tm_items (cost=0.00..2.56 rows=128 width=6,715) (actual time=0.265..32.862 rows=1,367 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.271 47.722 ↓ 896.0 896 1

Result (cost=5.19..12.39 rows=1 width=381) (actual time=39.242..47.722 rows=896 loops=1)

  • One-Time Filter: ((COALESCE(CURRENT_DATE, CURRENT_DATE) - '3650 days'::interval day) <= CURRENT_DATE)
25. 1.305 47.451 ↓ 896.0 896 1

Nested Loop Left Join (cost=5.19..12.39 rows=1 width=381) (actual time=39.238..47.451 rows=896 loops=1)

  • Join Filter: ((i.item_type)::text = (itemtype1_lang.item_type)::text)
26. 0.664 39.874 ↓ 896.0 896 1

Hash Join (cost=5.17..9.27 rows=1 width=226) (actual time=39.220..39.874 rows=896 loops=1)

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

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

28. 0.977 39.195 ↓ 896.0 896 1

Hash (cost=5.16..5.16 rows=1 width=201) (actual time=39.195..39.195 rows=896 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 478kB
29. 38.218 38.218 ↓ 896.0 896 1

CTE Scan on raw_items i (cost=0.00..5.16 rows=1 width=201) (actual time=0.413..38.218 rows=896 loops=1)

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

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

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

CTE geom_data

32. 0.134 52.863 ↓ 384.0 768 1

Append (cost=0.00..0.62 rows=2 width=341) (actual time=39.885..52.863 rows=768 loops=1)

33. 3.189 52.463 ↓ 768.0 768 1

Nested Loop (cost=0.00..0.30 rows=1 width=341) (actual time=39.885..52.463 rows=768 loops=1)

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

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

35. 49.176 49.176 ↓ 896.0 896 1

CTE Scan on homogenized_geoms d (cost=0.00..0.02 rows=1 width=373) (actual time=39.244..49.176 rows=896 loops=1)

36. 0.001 0.266 ↓ 0.0 0 1

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

38. 0.264 0.264 ↓ 0.0 0 1

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

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

CTE today

40. 0.021 0.021 ↑ 1.0 1 1

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

41.          

CTE raw_efs

42. 0.105 16.843 ↑ 1.0 2 1

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

43. 0.023 0.023 ↑ 1.0 1 1

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

44. 0.384 16.715 ↑ 1.0 2 1

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

45. 14.027 14.027 ↓ 384.0 768 1

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

46. 2.304 2.304 ↓ 0.0 0 768

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.003..0.003 rows=0 loops=768)

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

CTE schedules

48. 0.015 18.343 ↑ 1.0 2 1

Hash Right Join (cost=0.78..19.00 rows=2 width=93) (actual time=18.341..18.343 rows=2 loops=1)

  • Hash Cond: (s.enforcement_pattern_id = efs.enforcement_pattern_id)
  • Join Filter: ((efs.actual_start_date = s.start_date) AND (efs.actual_end_date = s.end_date))
49. 0.003 1.472 ↓ 0.0 0 1

Nested Loop (cost=0.71..18.89 rows=6 width=36) (actual time=1.472..1.472 rows=0 loops=1)

50. 0.573 1.451 ↑ 1.0 2 1

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

51. 0.110 0.110 ↓ 384.0 768 1

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

52. 0.768 0.768 ↓ 0.0 0 768

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=768)

  • Index Cond: (item_id = i_2.tm_item_id)
53. 0.018 0.018 ↓ 0.0 0 2

Index Scan using idx_enforcement_pattern_id on schedule s (cost=0.42..1.04 rows=7 width=36) (actual time=0.009..0.009 rows=0 loops=2)

  • Index Cond: (enforcement_pattern_id = ef_2.enforcement_pattern_id)
  • Filter: ((NOT cancelled) AND (actual_end_date > CURRENT_TIMESTAMP))
54. 0.007 16.856 ↑ 1.0 2 1

Hash (cost=0.04..0.04 rows=2 width=93) (actual time=16.856..16.856 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
55. 16.849 16.849 ↑ 1.0 2 1

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

56.          

CTE inforce_or_next_ef

57. 0.001 18.371 ↓ 2.0 2 1

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

  • Filter: (ef_3.rn = 1)
58. 0.012 18.370 ↓ 2.0 2 1

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

59. 0.010 18.358 ↓ 2.0 2 1

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

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

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

  • Filter: (actual_end_date > CURRENT_TIMESTAMP)
61. 39.991 39.991 ↓ 384.0 768 1

CTE Scan on geom_data gd (cost=0.00..0.04 rows=2 width=341) (actual time=39.888..39.991 rows=768 loops=1)

62. 0.005 18.380 ↓ 2.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
63. 18.375 18.375 ↓ 2.0 2 1

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

Planning time : 8.025 ms
Execution time : 61.413 ms