explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6b6K

Settings
# exclusive inclusive rows x rows loops node
1. 0.372 233.126 ↑ 1.2 1,338 1

Limit (cost=2,333.91..241,639.68 rows=1,617 width=853) (actual time=5.328..233.126 rows=1,338 loops=1)

2. 0.319 232.754 ↑ 1.2 1,338 1

Append (cost=2,333.91..241,639.68 rows=1,617 width=853) (actual time=5.328..232.754 rows=1,338 loops=1)

3. 164.350 175.672 ↑ 1.1 1,160 1

Hash Join (cost=2,333.91..9,799.48 rows=1,333 width=851) (actual time=5.327..175.672 rows=1,160 loops=1)

  • Hash Cond: (d.responsible_org_orgref = resporg.swa_org_ref)
4. 0.635 10.835 ↑ 1.1 1,160 1

Hash Join (cost=2,277.99..8,023.81 rows=1,333 width=815) (actual time=4.497..10.835 rows=1,160 loops=1)

  • Hash Cond: (d.publisher_orgref = publisher.swa_org_ref)
5. 0.847 9.777 ↑ 1.1 1,160 1

Nested Loop Left Join (cost=2,222.07..7,964.38 rows=1,333 width=597) (actual time=4.066..9.777 rows=1,160 loops=1)

  • Join Filter: (d.impact_score = ei_lang.impact_score)
6. 1.068 8.930 ↑ 1.1 1,160 1

Hash Join (cost=2,222.07..7,943.32 rows=1,333 width=379) (actual time=4.061..8.930 rows=1,160 loops=1)

  • Hash Cond: (d.impact_score = ei.impact_score)
7. 3.959 7.856 ↑ 1.1 1,160 1

Bitmap Heap Scan on entity_eton_agg d (cost=2,220.96..7,935.74 rows=1,333 width=291) (actual time=4.048..7.856 rows=1,160 loops=1)

  • Recheck Cond: ((location_point_easting >= 338465) AND (location_point_easting <= 439629) AND (location_point_northing >= 315609) AND (location_point_northing <= 379249) AND (start_date <= to_timestamp('05/02/2020 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('05/02/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
  • Filter: (active AND (publisher_swtype = 1) AND (impact_score = ANY ('{1,2,3,4}'::integer[])) AND (works_state = ANY ('{0,2,3,4,5,6,7,8}'::integer[])))
  • Rows Removed by Filter: 826
  • Heap Blocks: exact=1342
8. 3.897 3.897 ↓ 1.3 1,986 1

Bitmap Index Scan on idx_entity_eton_agg_coords_dates_impact (cost=0.00..2,220.62 rows=1,542 width=0) (actual time=3.897..3.897 rows=1,986 loops=1)

  • Index Cond: ((location_point_easting >= 338465) AND (location_point_easting <= 439629) AND (location_point_northing >= 315609) AND (location_point_northing <= 379249) AND (start_date <= to_timestamp('05/02/2020 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('05/02/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
9. 0.004 0.006 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=92) (actual time=0.006..0.006 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.002 0.002 ↑ 1.0 5 1

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

11. 0.000 0.000 ↓ 0.0 0 1,160

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

12. 0.003 0.003 ↓ 0.0 0 1

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

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 5
13. 0.220 0.423 ↑ 1.1 1,344 1

Hash (cost=37.63..37.63 rows=1,463 width=222) (actual time=0.423..0.423 rows=1,344 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 84kB
14. 0.203 0.203 ↑ 1.1 1,344 1

Seq Scan on orgref publisher (cost=0.00..37.63 rows=1,463 width=222) (actual time=0.002..0.203 rows=1,344 loops=1)

15. 0.245 0.487 ↑ 1.1 1,344 1

Hash (cost=37.63..37.63 rows=1,463 width=222) (actual time=0.487..0.487 rows=1,344 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 84kB
16. 0.242 0.242 ↑ 1.1 1,344 1

Seq Scan on orgref resporg (cost=0.00..37.63 rows=1,463 width=222) (actual time=0.005..0.242 rows=1,344 loops=1)

17. 19.064 56.763 ↑ 1.6 178 1

Nested Loop Left Join (cost=1.11..231,824.03 rows=284 width=866) (actual time=0.632..56.763 rows=178 loops=1)

  • Join Filter: ((d_1.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
  • Rows Removed by Join Filter: 7188
18. 0.554 37.343 ↑ 1.6 178 1

Nested Loop (cost=1.11..230,723.60 rows=284 width=1,034) (actual time=0.446..37.343 rows=178 loops=1)

  • Join Filter: ((d_1.entity_category)::text = (ecat.nsa_code)::text)
  • Rows Removed by Join Filter: 4792
19. 0.294 36.611 ↑ 1.6 178 1

Nested Loop Left Join (cost=1.11..230,328.36 rows=284 width=1,034) (actual time=0.430..36.611 rows=178 loops=1)

20. 0.167 36.139 ↑ 1.6 178 1

Nested Loop Left Join (cost=0.83..230,116.58 rows=284 width=816) (actual time=0.419..36.139 rows=178 loops=1)

21. 0.104 35.438 ↑ 1.6 178 1

Nested Loop Left Join (cost=0.56..229,904.80 rows=284 width=598) (actual time=0.408..35.438 rows=178 loops=1)

  • Join Filter: (d_1.impact_score = ei_lang_1.impact_score)
22. 0.221 35.334 ↑ 1.6 178 1

Nested Loop (cost=0.56..229,899.47 rows=284 width=380) (actual time=0.403..35.334 rows=178 loops=1)

  • Join Filter: (d_1.impact_score = ei_1.impact_score)
  • Rows Removed by Join Filter: 361
23. 35.067 35.113 ↑ 1.6 178 1

Index Scan using idx_entity_coords_dates_impact on entity d_1 (cost=0.56..229,879.96 rows=284 width=292) (actual time=0.393..35.113 rows=178 loops=1)

  • Index Cond: ((location_point_easting >= 338465) AND (location_point_easting <= 439629) AND (location_point_northing >= 315609) AND (location_point_northing <= 379249) AND (start_date <= to_timestamp('05/02/2020 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('05/02/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
  • Filter: (active AND (tm__entity_id_sw_xref IS NULL) AND (entity_type > 1) AND ((entitygroupid)::text = 'RW'::text) AND (published = 1) AND (impact_score = ANY ('{1,2,3,4}'::integer[])) AND (LOCALTIMESTAMP > publish_date) AND (((alternatives: SubPlan 1 or hashed SubPlan 2) AND (entity_type = 2)) OR (entity_type > 2)))
  • Rows Removed by Filter: 2300
24.          

SubPlan (for Index Scan)

25. 0.000 0.000 ↓ 0.0 0

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs f (cost=0.28..8.29 rows=1 width=0) (never executed)

  • Index Cond: ((extended_function_id = 14) AND (organisation_id = d_1.publisher_organisation_id))
  • Heap Fetches: 0
26. 0.030 0.046 ↑ 1.0 104 1

Bitmap Heap Scan on organisation_extended_funcs f_1 (cost=5.10..11.44 rows=107 width=4) (actual time=0.024..0.046 rows=104 loops=1)

  • Recheck Cond: (extended_function_id = 14)
  • Heap Blocks: exact=5
27. 0.016 0.016 ↑ 1.0 104 1

Bitmap Index Scan on organisation_extended_funcs_pkey (cost=0.00..5.08 rows=107 width=0) (actual time=0.016..0.016 rows=104 loops=1)

  • Index Cond: (extended_function_id = 14)
28. 0.000 0.000 ↑ 1.7 3 178

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

29. 0.003 0.003 ↑ 1.2 4 1

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

30. 0.000 0.000 ↓ 0.0 0 178

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

31. 0.003 0.003 ↓ 0.0 0 1

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

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 5
32. 0.534 0.534 ↑ 1.0 1 178

Index Scan using pk_orgref on orgref publisher_1 (cost=0.28..0.75 rows=1 width=222) (actual time=0.002..0.003 rows=1 loops=178)

  • Index Cond: (d_1.publisher_orgref = swa_org_ref)
33. 0.178 0.178 ↑ 1.0 1 178

Index Scan using pk_orgref on orgref resporg_1 (cost=0.28..0.75 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=178)

  • Index Cond: (d_1.responsible_org_orgref = swa_org_ref)
34. 0.170 0.178 ↑ 3.3 28 178

Materialize (cost=0.00..3.40 rows=93 width=118) (actual time=0.000..0.001 rows=28 loops=178)

35. 0.008 0.008 ↑ 2.7 34 1

Seq Scan on entity_category ecat (cost=0.00..2.93 rows=93 width=118) (actual time=0.004..0.008 rows=34 loops=1)

36. 0.340 0.356 ↓ 1.9 41 178

Materialize (cost=0.00..4.33 rows=22 width=22) (actual time=0.000..0.002 rows=41 loops=178)

37. 0.016 0.016 ↓ 2.3 50 1

Seq Scan on tm_nsa tmnsa (cost=0.00..4.22 rows=22 width=22) (actual time=0.002..0.016 rows=50 loops=1)

Planning time : 4.416 ms
Execution time : 233.759 ms