explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nq8L

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 215.827 ↑ 2.0 1 1

Unique (cost=62,319.05..62,319.14 rows=2 width=832) (actual time=215.826..215.827 rows=1 loops=1)

2.          

CTE gtab

3. 0.061 0.061 ↑ 1.0 1 1

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

4.          

CTE homogenized_geoms

5. 0.597 215.556 ↓ 30.0 30 1

Nested Loop Left Join (cost=10.65..62,318.16 rows=1 width=945) (actual time=3.350..215.556 rows=30 loops=1)

6. 8.498 214.839 ↓ 30.0 30 1

Nested Loop Left Join (cost=6.37..62,309.33 rows=1 width=383) (actual time=3.307..214.839 rows=30 loops=1)

  • Join Filter: (e_cte.publisher_organisation_id = lku_orgref.organisation_id)
  • Rows Removed by Join Filter: 38910
7. 0.036 193.771 ↓ 30.0 30 1

Nested Loop Left Join (cost=6.37..62,252.73 rows=1 width=365) (actual time=2.645..193.771 rows=30 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (lku_ec_lang.nsa_code)::text)
8. 0.799 193.465 ↓ 30.0 30 1

Nested Loop Left Join (cost=6.37..62,249.61 rows=1 width=335) (actual time=2.630..193.465 rows=30 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (lku_ec.nsa_code)::text)
  • Rows Removed by Join Filter: 2700
9. 0.955 192.066 ↓ 30.0 30 1

Nested Loop (cost=6.37..62,245.56 rows=1 width=313) (actual time=2.606..192.066 rows=30 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
  • Rows Removed by Join Filter: 2819
10. 185.903 185.931 ↓ 259.0 259 1

Index Scan using idx_entity_entitytype on entity e_cte (cost=6.37..62,242.16 rows=1 width=284) (actual time=1.736..185.931 rows=259 loops=1)

  • Index Cond: (entity_type = ANY ('{2,4,5}'::integer[]))
  • Filter: (active AND (location_point_easting >= 393214) AND (location_point_easting <= 480509) AND (location_point_northing >= 245607) AND (location_point_northing <= 308006) AND (COALESCE(tomtom__max_average_speed, '-1'::double precision) >= '-1'::double precision) AND (start_date <= to_date('31/10/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_date('01/10/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND ((entity_type <> 2) OR (hashed SubPlan 2)) AND (COALESCE((tm__sw_cancelled_status)::integer, 0) = 0))
  • Rows Removed by Filter: 290548
11.          

SubPlan (forIndex Scan)

12. 0.028 0.028 ↑ 1.0 82 1

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs (cost=0.27..5.72 rows=83 width=4) (actual time=0.010..0.028 rows=82 loops=1)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 0
13. 5.180 5.180 ↑ 1.0 11 259

Seq Scan on tm_nsa tmnsa (cost=0.00..3.26 rows=11 width=37) (actual time=0.003..0.020 rows=11 loops=259)

  • Filter: ((tm_nsa_type_id)::text = ANY ('{TMNSA21,TMNSA22,TMNSA23,TMNSA24,TMNSA01,TMNSA42,TMNSA41,TMNSA43,TMNSA46,TMNSA30,TMNSA32}'::text[]))
  • Rows Removed by Filter: 42
14. 0.600 0.600 ↑ 1.0 91 30

Seq Scan on entity_category lku_ec (cost=0.00..2.91 rows=91 width=29) (actual time=0.001..0.020 rows=91 loops=30)

15. 0.270 0.270 ↓ 0.0 0 30

Seq Scan on entity_category_lang lku_ec_lang (cost=0.00..3.11 rows=1 width=45) (actual time=0.009..0.009 rows=0 loops=30)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 89
16. 12.570 12.570 ↑ 1.0 1,298 30

Seq Scan on orgref lku_orgref (cost=0.00..40.38 rows=1,298 width=26) (actual time=0.001..0.419 rows=1,298 loops=30)

  • Filter: (active = 1)
  • Rows Removed by Filter: 12
17. 0.060 0.120 ↑ 1.0 1 30

Bitmap Heap Scan on la (cost=4.28..8.30 rows=1 width=15) (actual time=0.004..0.004 rows=1 loops=30)

  • Recheck Cond: (e_cte.lha_id = la_id)
  • Heap Blocks: exact=30
18. 0.060 0.060 ↑ 1.0 1 30

Bitmap Index Scan on la_pkey (cost=0.00..4.28 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=30)

  • Index Cond: (e_cte.lha_id = la_id)
19. 0.019 215.824 ↑ 2.0 1 1

Sort (cost=0.64..0.64 rows=2 width=832) (actual time=215.824..215.824 rows=1 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, d.tomtom__max_average_speed
  • Sort Method: quicksort Memory: 25kB
20. 0.004 215.805 ↑ 2.0 1 1

Append (cost=0.00..0.62 rows=2 width=832) (actual time=37.481..215.805 rows=1 loops=1)

21. 0.080 215.783 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.30 rows=1 width=832) (actual time=37.479..215.783 rows=1 loops=1)

  • Join Filter: ((d.location_bng && gtab.bbox) AND _st_intersects(d.location_bng, gtab.bbox))
  • Rows Removed by Join Filter: 29
22. 0.067 0.067 ↑ 1.0 1 1

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

23. 215.636 215.636 ↓ 30.0 30 1

CTE Scan on homogenized_geoms d (cost=0.00..0.02 rows=1 width=864) (actual time=3.353..215.636 rows=30 loops=1)

24. 0.001 0.018 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.30 rows=1 width=832) (actual time=0.018..0.018 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))
25. 0.002 0.002 ↑ 1.0 1 1

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

26. 0.015 0.015 ↓ 0.0 0 1

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

  • Filter: (location_bng IS NULL)
  • Rows Removed by Filter: 30