explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gbxs

Settings
# exclusive inclusive rows x rows loops node
1. 6,225.218 6,225.218 ↑ 61.7 78,829 1

CTE Scan on homogenized_geoms (cost=4,079,252.75..4,176,504.21 rows=4,862,573 width=888) (actual time=2.733..6,225.218 rows=78,829 loops=1)

2.          

CTE homogenized_geoms

3. 1,436.967 6,060.127 ↑ 61.7 78,829 1

Hash Left Join (cost=1,050.78..4,079,252.75 rows=4,862,573 width=917) (actual time=2.730..6,060.127 rows=78,829 loops=1)

  • Hash Cond: (e_cte.lha_id = la.la_id)
4. 43.417 4,621.654 ↑ 61.7 78,829 1

Hash Left Join (cost=20.21..2,691,737.46 rows=4,862,573 width=1,544) (actual time=1.189..4,621.654 rows=78,829 loops=1)

  • Hash Cond: (e_cte.publisher_organisation_id = lku_orgref.organisation_id)
5. 28.421 4,577.444 ↑ 64.0 75,984 1

Hash Left Join (cost=15.72..2,671,978.77 rows=4,862,573 width=1,330) (actual time=0.381..4,577.444 rows=75,984 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec_lang.nsa_code)::text)
6. 45.337 4,549.007 ↑ 64.0 75,984 1

Hash Left Join (cost=8.59..2,658,606.31 rows=4,862,573 width=822) (actual time=0.356..4,549.007 rows=75,984 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec.nsa_code)::text)
7. 37.719 4,503.620 ↑ 64.0 75,984 1

Hash Left Join (cost=4.50..2,645,256.28 rows=4,862,573 width=306) (actual time=0.297..4,503.620 rows=75,984 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
8. 4,465.855 4,465.855 ↑ 64.0 75,984 1

Seq Scan on entity e_cte (cost=0.00..2,631,426.82 rows=4,862,573 width=275) (actual time=0.239..4,465.855 rows=75,984 loops=1)

  • Filter: ((location_point_easting >= 1,372) AND (location_point_easting <= 613,199) AND (location_point_northing >= '-1204'::integer) AND (location_point_northing <= 670,276) 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: 572,339
9. 0.021 0.046 ↓ 2.4 53 1

Hash (cost=4.22..4.22 rows=22 width=39) (actual time=0.046..0.046 rows=53 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
10. 0.025 0.025 ↓ 2.4 53 1

Seq Scan on tm_nsa tmnsa (cost=0.00..4.22 rows=22 width=39) (actual time=0.003..0.025 rows=53 loops=1)

11. 0.029 0.050 ↑ 1.0 93 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
12. 0.021 0.021 ↑ 1.0 93 1

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

13. 0.001 0.016 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
14. 0.015 0.015 ↓ 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.015..0.015 rows=0 loops=1)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 90
15. 0.173 0.793 ↓ 41.6 291 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
16. 0.620 0.620 ↓ 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.028..0.620 rows=1,326 loops=1)

  • Index Cond: (active = 1)
  • Heap Fetches: 274
17. 0.129 1.506 ↑ 2.7 458 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 39kB
18. 1.377 1.377 ↑ 2.7 458 1

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

Planning time : 6.135 ms
Execution time : 6,240.308 ms