explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TZht

Settings
# exclusive inclusive rows x rows loops node
1. 112.246 112.246 ↓ 395.0 1,580 1

CTE Scan on homogenized_geoms (cost=69,397.86..69,397.94 rows=4 width=888) (actual time=26.471..112.246 rows=1,580 loops=1)

2.          

CTE homogenized_geoms

3. 8.332 109.899 ↓ 395.0 1,580 1

Nested Loop Left Join (cost=9,446.41..69,397.86 rows=4 width=917) (actual time=26.466..109.899 rows=1,580 loops=1)

4. 0.645 98.407 ↓ 395.0 1,580 1

Hash Left Join (cost=9,446.13..69,375.54 rows=4 width=1,559) (actual time=26.436..98.407 rows=1,580 loops=1)

  • Hash Cond: (e_cte.publisher_organisation_id = lku_orgref.organisation_id)
5. 2.469 97.294 ↓ 395.0 1,580 1

Hash Join (cost=9,441.65..69,371.03 rows=4 width=1,345) (actual time=25.961..97.294 rows=1,580 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
6. 4.157 94.768 ↓ 802.6 19,262 1

Hash Left Join (cost=9,436.99..69,366.30 rows=24 width=1,322) (actual time=25.412..94.768 rows=19,262 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec_lang.nsa_code)::text)
7. 5.253 90.594 ↓ 802.6 19,262 1

Hash Left Join (cost=9,429.85..69,359.10 rows=24 width=806) (actual time=25.389..90.594 rows=19,262 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec.nsa_code)::text)
8. 64.108 85.303 ↓ 802.6 19,262 1

Bitmap Heap Scan on entity e_cte (cost=9,425.76..69,354.95 rows=24 width=290) (actual time=25.341..85.303 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 1)) AND (COALESCE((tm__sw_cancelled_status)::integer, 0) = 0))
  • Rows Removed by Filter: 14126
  • Heap Blocks: exact=26211
9. 21.142 21.142 ↓ 2.1 33,388 1

Bitmap Index Scan on idx_entity_coords_dates_impact_egrpid (cost=0.00..9,411.57 rows=16,066 width=0) (actual time=21.142..21.142 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))
10.          

SubPlan (for Bitmap Heap Scan)

11. 0.053 0.053 ↑ 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.032..0.053 rows=104 loops=1)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 53
12. 0.019 0.038 ↑ 1.0 93 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
13. 0.019 0.019 ↑ 1.0 93 1

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

14. 0.000 0.017 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
15. 0.017 0.017 ↓ 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.017..0.017 rows=0 loops=1)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 90
16. 0.010 0.057 ↑ 1.0 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.047 0.047 ↑ 1.0 11 1

Seq Scan on tm_nsa tmnsa (cost=0.00..4.52 rows=11 width=39) (actual time=0.012..0.047 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
18. 0.102 0.468 ↓ 41.6 291 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
19. 0.366 0.366 ↓ 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.018..0.366 rows=1,326 loops=1)

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

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

  • Index Cond: (e_cte.lha_id = la_id)
Planning time : 1.914 ms
Execution time : 112.961 ms