explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vUHa

Settings
# exclusive inclusive rows x rows loops node
1. 849.252 849.252 ↑ 17.4 140 1

CTE Scan on homogenized_geoms (cost=2,689,438.13..2,689,486.85 rows=2,436 width=888) (actual time=58.279..849.252 rows=140 loops=1)

2.          

CTE homogenized_geoms

3. 2.388 848.763 ↑ 17.4 140 1

Hash Left Join (cost=198,289.04..2,689,438.13 rows=2,436 width=917) (actual time=58.275..848.763 rows=140 loops=1)

  • Hash Cond: (e_cte.lha_id = la.la_id)
4. 0.176 845.206 ↑ 17.4 140 1

Hash Left Join (cost=197,258.48..2,687,712.98 rows=2,436 width=1,544) (actual time=57.072..845.206 rows=140 loops=1)

  • Hash Cond: (e_cte.publisher_organisation_id = lku_orgref.organisation_id)
5. 0.928 844.538 ↑ 18.3 133 1

Hash Join (cost=197,253.99..2,687,698.60 rows=2,436 width=1,330) (actual time=56.572..844.538 rows=133 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
6. 1.687 843.544 ↑ 1.1 5,685 1

Hash Left Join (cost=197,248.97..2,687,675.92 rows=6,212 width=1,307) (actual time=55.855..843.544 rows=5,685 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec_lang.nsa_code)::text)
7. 2.276 841.840 ↑ 1.1 5,685 1

Hash Left Join (cost=197,241.84..2,687,651.70 rows=6,212 width=791) (actual time=55.832..841.840 rows=5,685 loops=1)

  • Hash Cond: ((e_cte.entity_category)::text = (lku_ec.nsa_code)::text)
8. 794.845 839.521 ↑ 1.1 5,685 1

Bitmap Heap Scan on entity e_cte (cost=197,237.74..2,687,630.56 rows=6,212 width=275) (actual time=55.777..839.521 rows=5,685 loops=1)

  • Recheck Cond: (entity_type = ANY ('{2,4,5}'::integer[]))
  • Rows Removed by Index Recheck: 1110
  • 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 ((NOT tm__hide_marker) OR (location_geom_type <> 1)) AND (published = 1) AND (publish_date < LOCALTIMESTAMP) AND (start_date <= to_timestamp('18/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)) AND ((entity_type <> 2) OR (hashed SubPlan 1)) AND (COALESCE((tm__sw_cancelled_status)::integer, 0) = 0))
  • Rows Removed by Filter: 319217
  • Heap Blocks: exact=59010 lossy=34835
9. 44.608 44.608 ↑ 25.7 340,370 1

Bitmap Index Scan on idx_entity_type_active (cost=0.00..197,222.01 rows=8,763,271 width=0) (actual time=44.608..44.608 rows=340,370 loops=1)

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

SubPlan (for Bitmap Heap Scan)

11. 0.068 0.068 ↑ 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.028..0.068 rows=104 loops=1)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 106
12. 0.023 0.043 ↑ 1.0 93 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
13. 0.020 0.020 ↑ 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.020 rows=93 loops=1)

14. 0.000 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.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
16. 0.013 0.066 ↑ 1.1 19 1

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

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

Seq Scan on tm_nsa tmnsa (cost=0.00..4.77 rows=20 width=39) (actual time=0.018..0.053 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
18. 0.104 0.492 ↓ 41.6 291 1

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

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

  • Index Cond: (active = 1)
  • Heap Fetches: 274
20. 0.088 1.169 ↑ 2.7 458 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 39kB
21. 1.081 1.081 ↑ 2.7 458 1

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

Planning time : 8.743 ms
Execution time : 849.949 ms