explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6fR

Settings
# exclusive inclusive rows x rows loops node
1. 145.668 145.668 ↑ 5.0 1 1

CTE Scan on homogenized_geoms (cost=285,967.31..285,967.41 rows=5 width=888) (actual time=81.959..145.668 rows=1 loops=1)

2.          

CTE homogenized_geoms

3. 0.042 145.663 ↑ 5.0 1 1

Nested Loop Left Join (cost=15.16..285,967.31 rows=5 width=916) (actual time=81.955..145.663 rows=1 loops=1)

  • Join Filter: (e_cte.lha_id = la.la_id)
  • Rows Removed by Join Filter: 281
4. 0.085 144.567 ↑ 5.0 1 1

Nested Loop Left Join (cost=15.16..284,855.71 rows=5 width=1,545) (actual time=80.859..144.567 rows=1 loops=1)

  • Join Filter: (e_cte.publisher_organisation_id = lku_orgref.organisation_id)
  • Rows Removed by Join Filter: 1325
5. 0.362 143.876 ↑ 5.0 1 1

Nested Loop (cost=14.88..284,850.77 rows=5 width=1,331) (actual time=80.702..143.876 rows=1 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
  • Rows Removed by Join Filter: 2896
6. 0.100 143.361 ↓ 10.9 153 1

Nested Loop Left Join (cost=14.88..284,841.75 rows=14 width=1,308) (actual time=8.156..143.361 rows=153 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (lku_ec_lang.nsa_code)::text)
7. 0.277 143.261 ↓ 10.9 153 1

Nested Loop Left Join (cost=14.88..284,834.41 rows=14 width=792) (actual time=8.135..143.261 rows=153 loops=1)

8. 142.461 142.525 ↓ 10.9 153 1

Index Scan using idx_entity_coords_dates_impact_egrpid on entity e_cte (cost=14.74..284,816.14 rows=14 width=276) (actual time=8.122..142.525 rows=153 loops=1)

  • Index Cond: ((location_point_easting >= 325593) AND (location_point_easting <= 454184) AND (location_point_northing >= 206334) AND (location_point_northing <= 271304) AND (start_date <= (LOCALTIMESTAMP + '1 day'::interval)) AND (end_date >= LOCALTIMESTAMP))
  • Filter: (active AND ((NOT tm__hide_marker) OR (location_geom_type <> 1)) AND (published = 1) AND (entity_type = ANY ('{2,4,5}'::integer[])) AND (publish_date < LOCALTIMESTAMP) AND ((entity_type <> 2) OR (hashed SubPlan 1)) AND (COALESCE((tm__sw_cancelled_status)::integer, 0) = 0))
  • Rows Removed by Filter: 1583
9.          

SubPlan (for Index Scan)

10. 0.064 0.064 ↑ 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.025..0.064 rows=104 loops=1)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 106
11. 0.459 0.459 ↑ 1.0 1 153

Index Scan using entity_category_pkey on entity_category lku_ec (cost=0.14..1.30 rows=1 width=634) (actual time=0.003..0.003 rows=1 loops=153)

  • Index Cond: ((e_cte.entity_category)::text = (nsa_code)::text)
12. 0.000 0.000 ↓ 0.0 0 153

Materialize (cost=0.00..7.13 rows=1 width=634) (actual time=0.000..0.000 rows=0 loops=153)

13. 0.019 0.019 ↓ 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.019..0.019 rows=0 loops=1)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 90
14. 0.118 0.153 ↑ 1.1 19 153

Materialize (cost=0.00..4.87 rows=20 width=39) (actual time=0.000..0.001 rows=19 loops=153)

15. 0.035 0.035 ↑ 1.1 19 1

Seq Scan on tm_nsa tmnsa (cost=0.00..4.77 rows=20 width=39) (actual time=0.011..0.035 rows=19 loops=1)

  • Filter: ((tm_nsa_type_id)::text = ANY ('{TMNSA38,TMNSA42,TMNSA41,TMNSA40,TMNSA43,TMNSA39,TMNSA21,TMNSA24,TMNSA50,TMNSA53,TMNSA54,TMNSA57,TMNSA60,TMNSA22,TMNSA45,TMNSA23,TMNSA44,TMNSA46,TMNSA01,NSA019}'::text[]))
  • Rows Removed by Filter: 34
16. 0.211 0.606 ↓ 189.4 1,326 1

Materialize (cost=0.28..4.44 rows=7 width=222) (actual time=0.027..0.606 rows=1,326 loops=1)

17. 0.395 0.395 ↓ 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.023..0.395 rows=1,326 loops=1)

  • Index Cond: (active = 1)
  • Heap Fetches: 274
18. 0.061 1.054 ↑ 4.3 282 1

Materialize (cost=0.00..1,021.38 rows=1,225 width=172) (actual time=0.008..1.054 rows=282 loops=1)

19. 0.993 0.993 ↑ 4.3 282 1

Seq Scan on la (cost=0.00..1,015.25 rows=1,225 width=172) (actual time=0.005..0.993 rows=282 loops=1)

Planning time : 6.424 ms
Execution time : 145.852 ms