explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qiZ8

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 299.434 ↓ 5.0 10 1

Append (cost=402,451.94..402,455.89 rows=2 width=824) (actual time=236.151..299.434 rows=10 loops=1)

2.          

CTE gtab

3. 0.074 0.074 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.073..0.074 rows=1 loops=1)

4.          

CTE homogenized_geoms

5. 0.275 299.208 ↓ 1.8 21 1

Nested Loop Left Join (cost=302,346.99..402,451.68 rows=12 width=918) (actual time=235.921..299.208 rows=21 loops=1)

6. 0.029 298.870 ↓ 1.8 21 1

Hash Left Join (cost=302,346.71..402,387.74 rows=12 width=1,548) (actual time=235.848..298.870 rows=21 loops=1)

  • Hash Cond: (e_cte.publisher_organisation_id = lku_orgref.organisation_id)
7. 0.105 298.357 ↓ 1.6 19 1

Hash Join (cost=302,342.23..402,383.20 rows=12 width=1,334) (actual time=235.349..298.357 rows=19 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
8. 0.177 298.205 ↓ 19.6 626 1

Hash Left Join (cost=302,337.21..402,378.08 rows=32 width=1,311) (actual time=234.893..298.205 rows=626 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec_lang.nsa_code)::text)
9. 0.250 298.010 ↓ 19.6 626 1

Hash Left Join (cost=302,330.07..402,370.86 rows=32 width=795) (actual time=234.865..298.010 rows=626 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec.nsa_code)::text)
10. 73.606 297.715 ↓ 19.6 626 1

Bitmap Heap Scan on entity e_cte (cost=302,325.98..402,366.69 rows=32 width=279) (actual time=234.804..297.715 rows=626 loops=1)

  • Recheck Cond: ((location_point_easting >= 303093) AND (location_point_easting <= 476684) AND (location_point_northing >= 183834) AND (location_point_northing <= 293804) AND (start_date <= '2020-02-18 00:00:00'::timestamp without time zone) AND (end_date >= '2020-02-11 00:00:00'::timestamp without time zone))
  • 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 2)) AND (COALESCE((tm__sw_cancelled_status)::integer, 0) = 0))
  • Rows Removed by Filter: 9690
  • Heap Blocks: exact=55765
11. 224.030 224.030 ↓ 3.5 96,298 1

Bitmap Index Scan on idx_entity_coords_dates_impact_egrpid (cost=0.00..302,311.79 rows=27,185 width=0) (actual time=224.030..224.030 rows=96,298 loops=1)

  • Index Cond: ((location_point_easting >= 303093) AND (location_point_easting <= 476684) AND (location_point_northing >= 183834) AND (location_point_northing <= 293804) AND (start_date <= '2020-02-18 00:00:00'::timestamp without time zone) AND (end_date >= '2020-02-11 00:00:00'::timestamp without time zone))
12.          

SubPlan (for Bitmap Heap Scan)

13. 0.079 0.079 ↑ 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.039..0.079 rows=104 loops=1)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 106
14. 0.022 0.045 ↑ 1.0 93 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
15. 0.023 0.023 ↑ 1.0 93 1

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

16. 0.001 0.018 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
17. 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
18. 0.008 0.047 ↑ 1.1 19 1

Hash (cost=4.77..4.77 rows=20 width=39) (actual time=0.047..0.047 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.039 0.039 ↑ 1.1 19 1

Seq Scan on tm_nsa tmnsa (cost=0.00..4.77 rows=20 width=39) (actual time=0.014..0.039 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
20. 0.103 0.484 ↓ 41.6 291 1

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

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

  • Index Cond: (active = 1)
  • Heap Fetches: 274
22. 0.063 0.063 ↑ 1.0 1 21

Index Scan using la_pkey on la (cost=0.28..5.04 rows=1 width=172) (actual time=0.003..0.003 rows=1 loops=21)

  • Index Cond: (e_cte.lha_id = la_id)
23. 0.085 299.419 ↓ 10.0 10 1

Nested Loop (cost=0.00..3.41 rows=1 width=824) (actual time=236.150..299.419 rows=10 loops=1)

  • Join Filter: ((d.location_bng && gtab.bbox) AND _st_intersects(d.location_bng, gtab.bbox))
  • Rows Removed by Join Filter: 11
24. 0.077 0.077 ↑ 1.0 1 1

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

25. 299.257 299.257 ↓ 1.8 21 1

CTE Scan on homogenized_geoms d (cost=0.00..0.24 rows=12 width=856) (actual time=235.924..299.257 rows=21 loops=1)

26. 0.002 0.013 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.52 rows=1 width=824) (actual time=0.012..0.013 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))
27. 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.001..0.002 rows=1 loops=1)

28. 0.009 0.009 ↓ 0.0 0 1

CTE Scan on homogenized_geoms d_1 (cost=0.00..0.24 rows=1 width=856) (actual time=0.009..0.009 rows=0 loops=1)

  • Filter: (location_bng IS NULL)
  • Rows Removed by Filter: 21
Planning time : 6.880 ms
Execution time : 300.187 ms