explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3hee

Settings
# exclusive inclusive rows x rows loops node
1. 7,912.340 7,912.340 ↑ 1.5 107,846 1

CTE Scan on homogenized_geoms (cost=939,888.18..943,151.44 rows=163,163 width=888) (actual time=1,475.671..7,912.340 rows=107,846 loops=1)

2.          

CTE homogenized_geoms

3. 2,611.680 7,632.456 ↑ 1.5 107,846 1

Hash Left Join (cost=371,490.47..939,888.18 rows=163,163 width=917) (actual time=1,475.666..7,632.456 rows=107,846 loops=1)

  • Hash Cond: (e_cte.lha_id = la.la_id)
4. 72.309 5,018.933 ↑ 1.5 107,846 1

Hash Left Join (cost=370,459.90..892,334.31 rows=163,163 width=1,540) (actual time=1,473.770..5,018.933 rows=107,846 loops=1)

  • Hash Cond: (e_cte.publisher_organisation_id = lku_orgref.organisation_id)
5. 49.614 4,945.732 ↑ 1.6 104,249 1

Hash Left Join (cost=370,455.42..891,666.81 rows=163,163 width=1,326) (actual time=1,472.861..4,945.732 rows=104,249 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec_lang.nsa_code)::text)
6. 75.537 4,896.091 ↑ 1.6 104,249 1

Hash Left Join (cost=370,448.28..891,211.20 rows=163,163 width=818) (actual time=1,472.813..4,896.091 rows=104,249 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec.nsa_code)::text)
7. 67.659 4,820.488 ↑ 1.6 104,249 1

Hash Left Join (cost=370,444.19..890,759.30 rows=163,163 width=302) (actual time=1,472.729..4,820.488 rows=104,249 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
8. 3,292.030 4,752.771 ↑ 1.6 104,249 1

Bitmap Heap Scan on entity e_cte (cost=370,439.69..890,293.49 rows=163,163 width=271) (actual time=1,472.642..4,752.771 rows=104,249 loops=1)

  • Recheck Cond: ((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))
  • Rows Removed by Index Recheck: 314323
  • Filter: ((location_point_easting >= '-21128'::integer) AND (location_point_easting <= 635699) AND (location_point_northing >= '-23704'::integer) AND (location_point_northing <= 692776))
  • Rows Removed by Filter: 4288
  • Heap Blocks: exact=35165 lossy=1584709
9. 1,460.741 1,460.741 ↓ 65.3 11,153,547 1

Bitmap Index Scan on idx_entity_dates (cost=0.00..370,398.90 rows=170,799 width=0) (actual time=1,460.741..1,460.741 rows=11,153,547 loops=1)

  • Index Cond: ((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))
10. 0.025 0.058 ↓ 2.4 53 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
11. 0.033 0.033 ↓ 2.4 53 1

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

12. 0.036 0.066 ↑ 1.0 93 1

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

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

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

14. 0.000 0.027 ↓ 0.0 0 1

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

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

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

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

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

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

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

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

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

Planning time : 9.321 ms
Execution time : 7,943.648 ms