explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mI89

Settings
# exclusive inclusive rows x rows loops node
1. 724.066 724.066 ↓ 1,580.0 1,580 1

CTE Scan on homogenized_geoms (cost=70,099.83..70,099.85 rows=1 width=888) (actual time=25.954..724.066 rows=1,580 loops=1)

2.          

CTE homogenized_geoms

3. 10.272 721.294 ↓ 1,580.0 1,580 1

Nested Loop Left Join (cost=9,514.45..70,099.83 rows=1 width=917) (actual time=25.949..721.294 rows=1,580 loops=1)

4. 1.480 707.862 ↓ 1,580.0 1,580 1

Nested Loop Left Join (cost=9,514.17..70,091.24 rows=1 width=1,559) (actual time=25.919..707.862 rows=1,580 loops=1)

5. 11.566 701.642 ↓ 1,580.0 1,580 1

Nested Loop (cost=9,513.89..70,086.93 rows=1 width=1,345) (actual time=25.903..701.642 rows=1,580 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
  • Rows Removed by Join Filter: 202238
6. 8.293 362.622 ↓ 19,262.0 19,262 1

Nested Loop Left Join (cost=9,513.89..70,082.27 rows=1 width=1,322) (actual time=25.119..362.622 rows=19,262 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (lku_ec_lang.nsa_code)::text)
7. 69.107 200.233 ↓ 19,262.0 19,262 1

Nested Loop Left Join (cost=9,513.89..70,075.13 rows=1 width=806) (actual time=25.101..200.233 rows=19,262 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (lku_ec.nsa_code)::text)
  • Rows Removed by Join Filter: 590482
8. 71.692 92.602 ↓ 19,262.0 19,262 1

Bitmap Heap Scan on entity e_cte (cost=9,513.89..70,071.04 rows=1 width=290) (actual time=25.088..92.602 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 (COALESCE(responsible_org_organisation_id, 0) >= 0) AND (COALESCE(responsible_org_organisation_id, 0) <= 9999) 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. 20.859 20.859 ↓ 2.1 33,388 1

Bitmap Index Scan on idx_entity_coords_dates_impact_egrpid (cost=0.00..9,499.71 rows=16,219 width=0) (actual time=20.859..20.859 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.051 0.051 ↑ 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.031..0.051 rows=104 loops=1)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 53
12. 38.524 38.524 ↑ 2.9 32 19,262

Seq Scan on entity_category lku_ec (cost=0.00..2.93 rows=93 width=634) (actual time=0.001..0.002 rows=32 loops=19,262)

13. 154.096 154.096 ↓ 0.0 0 19,262

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

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 90
14. 327.454 327.454 ↑ 1.0 11 19,262

Seq Scan on tm_nsa tmnsa (cost=0.00..4.52 rows=11 width=39) (actual time=0.002..0.017 rows=11 loops=19,262)

  • Filter: ((tm_nsa_type_id)::text = ANY ('{TMNSA21,TMNSA22,TMNSA23,TMNSA24,TMNSA01,TMNSA42,TMNSA41,TMNSA43,TMNSA46,TMNSA30,TMNSA32}'::text[]))
  • Rows Removed by Filter: 40
15. 4.740 4.740 ↑ 1.0 1 1,580

Index Only Scan using idx_lku_orgref_123 on orgref lku_orgref (cost=0.28..4.30 rows=1 width=222) (actual time=0.003..0.003 rows=1 loops=1,580)

  • Index Cond: ((active = 1) AND (organisation_id = e_cte.publisher_organisation_id))
  • Heap Fetches: 1530
16. 3.160 3.160 ↑ 1.0 1 1,580

Index Scan using la_pkey on la (cost=0.28..8.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.764 ms
Execution time : 724.813 ms