explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SOas

Settings
# exclusive inclusive rows x rows loops node
1. 688.291 688.291 ↑ 2,446.4 347 1

CTE Scan on homogenized_geoms (cost=2,770,460.82..2,787,439.16 rows=848,917 width=888) (actual time=48.708..688.291 rows=347 loops=1)

2.          

CTE homogenized_geoms

3. 3.367 687.564 ↑ 2,446.4 347 1

Hash Left Join (cost=198,829.40..2,770,460.82 rows=848,917 width=917) (actual time=48.705..687.564 rows=347 loops=1)

  • Hash Cond: (e_cte.lha_id = la.la_id)
4. 0.259 682.041 ↑ 2,446.4 347 1

Hash Left Join (cost=197,798.83..2,527,375.19 rows=848,917 width=1,544) (actual time=46.513..682.041 rows=347 loops=1)

  • Hash Cond: (e_cte.publisher_organisation_id = lku_orgref.organisation_id)
5. 0.198 681.173 ↑ 2,489.5 341 1

Hash Left Join (cost=197,794.35..2,523,921.98 rows=848,917 width=1,330) (actual time=45.893..681.173 rows=341 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec_lang.nsa_code)::text)
6. 0.242 680.958 ↑ 2,489.5 341 1

Hash Left Join (cost=197,787.21..2,521,581.50 rows=848,917 width=822) (actual time=45.869..680.958 rows=341 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec.nsa_code)::text)
7. 1.114 680.674 ↑ 2,489.5 341 1

Hash Join (cost=197,783.12..2,519,247.45 rows=848,917 width=306) (actual time=45.812..680.674 rows=341 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
8. 646.011 679.514 ↑ 381.7 5,672 1

Bitmap Heap Scan on entity e_cte (cost=197,778.10..2,513,087.78 rows=2,164,739 width=275) (actual time=45.218..679.514 rows=5,672 loops=1)

  • Recheck Cond: (entity_type = ANY ('{2,4,5}'::integer[]))
  • Rows Removed by Index Recheck: 1109
  • Filter: (active AND (location_point_easting >= '-21128'::integer) AND (location_point_easting <= 635699) AND (location_point_northing >= '-23704'::integer) AND (location_point_northing <= 692776) AND (start_date <= to_timestamp('12/02/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('11/02/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
  • Rows Removed by Filter: 319239
  • Heap Blocks: exact=59014 lossy=34838
9. 33.503 33.503 ↑ 25.7 340,392 1

Bitmap Index Scan on idx_entity_type_active (cost=0.00..197,236.91 rows=8,763,961 width=0) (actual time=33.503..33.503 rows=340,392 loops=1)

  • Index Cond: ((entity_type = ANY ('{2,4,5}'::integer[])) AND (active = true))
10. 0.008 0.046 ↑ 1.1 19 1

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

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

Seq Scan on tm_nsa tmnsa (cost=0.00..4.77 rows=20 width=39) (actual time=0.013..0.038 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
12. 0.023 0.042 ↑ 1.0 93 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
13. 0.019 0.019 ↑ 1.0 93 1

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

14. 0.001 0.017 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
15. 0.016 0.016 ↓ 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.016..0.016 rows=0 loops=1)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 90
16. 0.112 0.609 ↓ 41.6 291 1

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

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

  • Index Cond: (active = 1)
  • Heap Fetches: 274
18. 0.106 2.156 ↑ 2.7 458 1

Hash (cost=1,015.25..1,015.25 rows=1,225 width=172) (actual time=2.156..2.156 rows=458 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 39kB
19. 2.050 2.050 ↑ 2.7 458 1

Seq Scan on la (cost=0.00..1,015.25 rows=1,225 width=172) (actual time=0.003..2.050 rows=458 loops=1)

Planning time : 8.871 ms
Execution time : 688.928 ms