explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QHdb

Settings
# exclusive inclusive rows x rows loops node
1. 0.357 199.423 ↓ 44.0 44 1

Nested Loop (cost=6,404.63..6,404.93 rows=1 width=832) (actual time=77.070..199.423 rows=44 loops=1)

  • Join Filter: ((d.location_bng && gtab.bbox) AND _st_intersects(d.location_bng, gtab.bbox))
  • Rows Removed by Join Filter: 171
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. 3.172 198.558 ↓ 215.0 215 1

Nested Loop Left Join (cost=10.78..6,404.37 rows=1 width=945) (actual time=1.635..198.558 rows=215 loops=1)

6. 60.602 194.741 ↓ 215.0 215 1

Nested Loop Left Join (cost=6.50..6,395.54 rows=1 width=383) (actual time=1.594..194.741 rows=215 loops=1)

  • Join Filter: (e_cte.publisher_organisation_id = lku_orgref.organisation_id)
  • Rows Removed by Join Filter: 278855
7. 0.153 44.484 ↓ 215.0 215 1

Nested Loop Left Join (cost=6.50..6,338.94 rows=1 width=365) (actual time=0.893..44.484 rows=215 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (lku_ec_lang.nsa_code)::text)
8. 5.704 42.396 ↓ 215.0 215 1

Nested Loop Left Join (cost=6.50..6,335.82 rows=1 width=335) (actual time=0.878..42.396 rows=215 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (lku_ec.nsa_code)::text)
  • Rows Removed by Join Filter: 19350
9. 3.714 32.392 ↓ 215.0 215 1

Nested Loop (cost=6.50..6,331.77 rows=1 width=313) (actual time=0.854..32.392 rows=215 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
  • Rows Removed by Join Filter: 10642
10. 8.910 8.938 ↓ 987.0 987 1

Index Scan using idx_entity_type_coords_dates on entity e_cte (cost=6.50..6,328.37 rows=1 width=284) (actual time=0.197..8.938 rows=987 loops=1)

  • Index Cond: ((entity_type = ANY ('{2,4,5}'::integer[])) AND (location_point_easting >= 375000) AND (location_point_easting <= 475000) AND (location_point_northing >= 175000) AND (location_point_northing <= 275000) AND (start_date <= to_date('31/12/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)))
  • Filter: (active AND (COALESCE(tomtom__max_average_speed, '-1'::double precision) >= '-1'::double precision) AND ((entity_type <> 2) OR (hashed SubPlan 2)) AND (COALESCE((tm__sw_cancelled_status)::integer, 0) = 0))
  • Rows Removed by Filter: 555
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.008..0.028 rows=82 loops=1)

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

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

  • 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. 4.300 4.300 ↑ 1.0 91 215

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=215)

15. 1.935 1.935 ↓ 0.0 0 215

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=215)

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

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

  • Filter: (active = 1)
  • Rows Removed by Filter: 12
17. 0.215 0.645 ↑ 1.0 1 215

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

  • Recheck Cond: (e_cte.lha_id = la_id)
  • Heap Blocks: exact=213
18. 0.430 0.430 ↑ 1.0 1 215

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

  • Index Cond: (e_cte.lha_id = la_id)
19. 0.066 0.066 ↑ 1.0 1 1

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

20. 199.000 199.000 ↓ 215.0 215 1

CTE Scan on homogenized_geoms d (cost=0.00..0.02 rows=1 width=864) (actual time=1.638..199.000 rows=215 loops=1)