explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HkHf

Settings
# exclusive inclusive rows x rows loops node
1. 0.388 1,428.163 ↓ 33.0 759 1

Limit (cost=273,457.67..382,760.91 rows=23 width=491) (actual time=992.368..1,428.163 rows=759 loops=1)

2. 330.869 1,427.775 ↓ 33.0 759 1

Nested Loop (cost=273,457.67..382,760.91 rows=23 width=491) (actual time=992.366..1,427.775 rows=759 loops=1)

  • Join Filter: (d.responsible_org_orgref = resporg.swa_org_ref)
  • Rows Removed by Join Filter: 993531
3. 0.436 0.436 ↑ 1.0 1,310 1

Seq Scan on orgref resporg (cost=0.00..37.10 rows=1,310 width=26) (actual time=0.004..0.436 rows=1,310 loops=1)

4. 199.294 1,096.470 ↓ 33.0 759 1,310

Materialize (cost=273,457.67..382,248.52 rows=23 width=642) (actual time=0.618..0.837 rows=759 loops=1,310)

5. 298.137 897.176 ↓ 33.0 759 1

Nested Loop (cost=273,457.67..382,248.40 rows=23 width=642) (actual time=808.995..897.176 rows=759 loops=1)

  • Join Filter: (d.publisher_orgref = publisher.swa_org_ref)
  • Rows Removed by Join Filter: 993531
6. 0.369 0.369 ↑ 1.0 1,310 1

Seq Scan on orgref publisher (cost=0.00..37.10 rows=1,310 width=26) (actual time=0.002..0.369 rows=1,310 loops=1)

7. 197.091 598.670 ↓ 33.0 759 1,310

Materialize (cost=273,457.67..381,759.41 rows=23 width=620) (actual time=0.290..0.457 rows=759 loops=1,310)

8. 1.611 401.579 ↓ 33.0 759 1

Nested Loop (cost=273,457.67..381,759.29 rows=23 width=620) (actual time=379.312..401.579 rows=759 loops=1)

  • Join Filter: (d.impact_score = ei.impact_score)
  • Rows Removed by Join Filter: 3036
9. 0.741 399.209 ↓ 33.0 759 1

Nested Loop Left Join (cost=273,457.67..381,756.51 rows=23 width=532) (actual time=379.297..399.209 rows=759 loops=1)

  • Join Filter: (d.impact_score = ei_lang.impact_score)
10. 22.172 398.468 ↓ 33.0 759 1

Bitmap Heap Scan on entity_agg_repopulate d (cost=273,457.67..381,755.10 rows=23 width=314) (actual time=379.281..398.468 rows=759 loops=1)

  • Recheck Cond: ((location_point_easting >= 500000) AND (location_point_easting <= 550000) AND (location_point_northing >= 200000) AND (location_point_northing <= 250000) AND (start_date <= to_timestamp('31/12/2018 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('01/09/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
  • Filter: ((tomtom__max_average_speed IS NOT NULL) AND (entity_type = 1) AND (impact_score = ANY ('{1,2,3,4}'::integer[])))
  • Rows Removed by Filter: 32073
  • Heap Blocks: exact=15515
11. 376.296 376.296 ↓ 1.1 32,832 1

Bitmap Index Scan on idx_entity_agg_repopulate_coords_dates_impact (cost=0.00..273,457.67 rows=30,843 width=0) (actual time=376.296..376.296 rows=32,832 loops=1)

  • Index Cond: ((location_point_easting >= 500000) AND (location_point_easting <= 550000) AND (location_point_northing >= 200000) AND (location_point_northing <= 250000) AND (start_date <= to_timestamp('31/12/2018 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('01/09/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
12. 0.000 0.000 ↓ 0.0 0 759

Materialize (cost=0.00..1.07 rows=1 width=222) (actual time=0.000..0.000 rows=0 loops=759)

13. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on eton_impact_lang ei_lang (cost=0.00..1.06 rows=1 width=222) (actual time=0.011..0.011 rows=0 loops=1)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 5
14. 0.755 0.759 ↑ 1.0 5 759

Materialize (cost=0.00..1.07 rows=5 width=92) (actual time=0.000..0.001 rows=5 loops=759)

15. 0.004 0.004 ↑ 1.0 5 1

Seq Scan on eton_impact ei (cost=0.00..1.05 rows=5 width=92) (actual time=0.002..0.004 rows=5 loops=1)