explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vTbV

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 115.776 ↓ 0.0 0 1

Unique (cost=69,524.02..69,524.10 rows=2 width=824) (actual time=115.776..115.776 rows=0 loops=1)

2.          

CTE gtab

3. 0.072 0.072 ↑ 1.0 1 1

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

4.          

CTE homogenized_geoms

5. 8.478 111.997 ↓ 316.0 1,580 1

Nested Loop Left Join (cost=9,126.11..69,521.91 rows=5 width=917) (actual time=26.228..111.997 rows=1,580 loops=1)

6. 0.672 100.359 ↓ 316.0 1,580 1

Hash Left Join (cost=9,125.83..69,494.22 rows=5 width=1,560) (actual time=26.189..100.359 rows=1,580 loops=1)

  • Hash Cond: (e_cte.publisher_organisation_id = lku_orgref.organisation_id)
7. 2.472 99.210 ↓ 316.0 1,580 1

Hash Join (cost=9,121.35..69,489.70 rows=5 width=1,346) (actual time=25.699..99.210 rows=1,580 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
8. 4.135 96.682 ↓ 770.5 19,262 1

Hash Left Join (cost=9,116.69..69,484.98 rows=25 width=1,323) (actual time=25.123..96.682 rows=19,262 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec_lang.nsa_code)::text)
9. 5.273 92.529 ↓ 770.5 19,262 1

Hash Left Join (cost=9,109.55..69,477.77 rows=25 width=807) (actual time=25.099..92.529 rows=19,262 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec.nsa_code)::text)
10. 66.259 87.211 ↓ 770.5 19,262 1

Bitmap Heap Scan on entity e_cte (cost=9,105.46..69,473.61 rows=25 width=291) (actual time=25.039..87.211 rows=19,262 loops=1)

  • Recheck Cond: ((location_point_easting >= 393214) AND (location_point_easting <= 480509) AND (location_point_northing >= 245607) AND (location_point_northing <= 308006) AND (start_date <= (LOCALTIMESTAMP + '1 day'::interval)) AND (end_date <= LOCALTIMESTAMP))
  • Filter: (active AND ((NOT tm__hide_marker) OR (location_geom_type <> 1)) AND (entity_type = ANY ('{2,4,5}'::integer[])) AND ((entity_type <> 2) OR (hashed SubPlan 2)) AND (COALESCE((tm__sw_cancelled_status)::integer, 0) = 0))
  • Rows Removed by Filter: 14126
  • Heap Blocks: exact=26211
11. 20.897 20.897 ↓ 2.1 33,388 1

Bitmap Index Scan on idx_entity_coords_dates_impact_egrpid (cost=0.00..9,091.27 rows=16,189 width=0) (actual time=20.897..20.897 rows=33,388 loops=1)

  • Index Cond: ((location_point_easting >= 393214) AND (location_point_easting <= 480509) AND (location_point_northing >= 245607) AND (location_point_northing <= 308006) AND (start_date <= (LOCALTIMESTAMP + '1 day'::interval)) AND (end_date <= LOCALTIMESTAMP))
12.          

SubPlan (for Bitmap Heap Scan)

13. 0.055 0.055 ↑ 1.0 104 1

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs (cost=0.28..13.91 rows=107 width=4) (actual time=0.035..0.055 rows=104 loops=1)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 53
14. 0.023 0.045 ↑ 1.0 93 1

Hash (cost=2.93..2.93 rows=93 width=634) (actual time=0.045..0.045 rows=93 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
15. 0.022 0.022 ↑ 1.0 93 1

Seq Scan on entity_category lku_ec (cost=0.00..2.93 rows=93 width=634) (actual time=0.005..0.022 rows=93 loops=1)

16. 0.000 0.018 ↓ 0.0 0 1

Hash (cost=7.12..7.12 rows=1 width=634) (actual time=0.018..0.018 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
17. 0.018 0.018 ↓ 0.0 0 1

Seq Scan on entity_category_lang lku_ec_lang (cost=0.00..7.12 rows=1 width=634) (actual time=0.018..0.018 rows=0 loops=1)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 90
18. 0.014 0.056 ↑ 1.0 11 1

Hash (cost=4.52..4.52 rows=11 width=39) (actual time=0.056..0.056 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.042 0.042 ↑ 1.0 11 1

Seq Scan on tm_nsa tmnsa (cost=0.00..4.52 rows=11 width=39) (actual time=0.008..0.042 rows=11 loops=1)

  • Filter: ((tm_nsa_type_id)::text = ANY ('{TMNSA21,TMNSA22,TMNSA23,TMNSA24,TMNSA01,TMNSA42,TMNSA41,TMNSA43,TMNSA46,TMNSA30,TMNSA32}'::text[]))
  • Rows Removed by Filter: 42
20. 0.106 0.477 ↓ 41.6 291 1

Hash (cost=4.40..4.40 rows=7 width=222) (actual time=0.477..0.477 rows=291 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
21. 0.371 0.371 ↓ 189.4 1,326 1

Index Only Scan using idx_lku_orgref_123 on orgref lku_orgref (cost=0.28..4.40 rows=7 width=222) (actual time=0.022..0.371 rows=1,326 loops=1)

  • Index Cond: (active = 1)
  • Heap Fetches: 274
22. 3.160 3.160 ↑ 1.0 1 1,580

Index Scan using la_pkey on la (cost=0.28..5.25 rows=1 width=172) (actual time=0.002..0.002 rows=1 loops=1,580)

  • Index Cond: (e_cte.lha_id = la_id)
23. 0.017 115.775 ↓ 0.0 0 1

Sort (cost=1.85..1.85 rows=2 width=824) (actual time=115.775..115.775 rows=0 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
  • Sort Method: quicksort Memory: 25kB
24. 0.001 115.758 ↓ 0.0 0 1

Append (cost=0.00..1.84 rows=2 width=824) (actual time=115.758..115.758 rows=0 loops=1)

25. 0.737 115.198 ↓ 0.0 0 1

Nested Loop (cost=0.00..1.43 rows=1 width=824) (actual time=115.198..115.198 rows=0 loops=1)

  • Join Filter: ((d.location_bng && gtab.bbox) AND _st_intersects(d.location_bng, gtab.bbox))
  • Rows Removed by Join Filter: 1580
26. 0.075 0.075 ↑ 1.0 1 1

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

27. 114.386 114.386 ↓ 316.0 1,580 1

CTE Scan on homogenized_geoms d (cost=0.00..0.10 rows=5 width=856) (actual time=26.231..114.386 rows=1,580 loops=1)

28. 0.125 0.559 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.38 rows=1 width=824) (actual time=0.559..0.559 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))
  • Rows Removed by Join Filter: 432
29. 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)

30. 0.433 0.433 ↓ 432.0 432 1

CTE Scan on homogenized_geoms d_1 (cost=0.00..0.10 rows=1 width=856) (actual time=0.035..0.433 rows=432 loops=1)

  • Filter: (location_bng IS NULL)
  • Rows Removed by Filter: 1148
Planning time : 1.984 ms
Execution time : 116.293 ms