explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OL6G

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 849.016 ↑ 24.0 2 1

Subquery Scan on roadworks__impact123_datenow_published (cost=4,034.22..100,231.02 rows=48 width=1,014) (actual time=22.610..849.016 rows=2 loops=1)

  • Filter: ((roadworks__impact123_datenow_published.shape_id IS NOT NULL) AND ((roadworks__impact123_datenow_published.shape_id = 'triangle'::text) OR (roadworks__impact123_datenow_published.shape_id = 'triangle-traffman'::text) OR (roadworks__impact123_datenow_published.shape_id = 'square'::text) OR (roadworks__impact123_datenow_published.shape_id = 'square-traffman'::text)))
2. 0.003 849.003 ↑ 1,222.5 2 1

Append (cost=4,034.22..100,178.76 rows=2,445 width=1,014) (actual time=22.601..849.003 rows=2 loops=1)

3. 0.004 27.429 ↑ 1,222.0 2 1

Subquery Scan on *SELECT* 1 (cost=4,034.22..36,499.64 rows=2,444 width=688) (actual time=22.600..27.429 rows=2 loops=1)

4. 0.809 27.425 ↑ 1,222.0 2 1

Hash Join (cost=4,034.22..36,462.98 rows=2,444 width=684) (actual time=22.598..27.425 rows=2 loops=1)

  • Hash Cond: (d.impact_score = ei.impact_score)
5. 0.009 26.605 ↑ 1,222.0 2 1

Hash Join (cost=4,016.57..33,304.41 rows=2,444 width=405) (actual time=21.985..26.605 rows=2 loops=1)

  • Hash Cond: (d.responsible_org_orgref = resporg.swa_org_ref)
6. 0.012 25.745 ↑ 1,222.0 2 1

Hash Join (cost=3,964.12..33,245.52 rows=2,444 width=383) (actual time=21.127..25.745 rows=2 loops=1)

  • Hash Cond: (d.publisher_orgref = publisher.swa_org_ref)
7. 17.469 24.945 ↑ 1,222.0 2 1

Bitmap Heap Scan on entity_eton_agg d (cost=3,911.67..33,186.63 rows=2,444 width=361) (actual time=20.330..24.945 rows=2 loops=1)

  • Recheck Cond: ((start_date <= ((CURRENT_DATE + '1 day'::interval) - '00:00:01'::interval)) AND (end_date >= LOCALTIMESTAMP) AND (end_date >= (CURRENT_DATE)::timestamp without time zone) AND (works_state = ANY ('{0,2,3,4,5,6,7,8}'::integer[])))
  • Filter: (active AND (st_transform(location_point_bng, 4326) && '0103000020E6100000010000000500000055F4EFFFBFD9CABF62E00BEA08C8494055F4EFFFBFD9CABFF5031BA612C9494072F1F0FF3F2EC9BFF5031BA612C9494072F1F0FF3F2EC9BF62E00BEA08C8494055F4EFFFBFD9CABF62E00BEA08C84940'::geometry))
  • Rows Removed by Filter: 8758
  • Heap Blocks: exact=5392
8. 7.476 7.476 ↑ 1.4 8,760 1

Bitmap Index Scan on idx_entity_eton_agg_active_dates_works_state_impact (cost=0.00..3,911.06 rows=12,220 width=0) (actual time=7.476..7.476 rows=8,760 loops=1)

  • Index Cond: ((active = true) AND (start_date <= ((CURRENT_DATE + '1 day'::interval) - '00:00:01'::interval)) AND (end_date >= LOCALTIMESTAMP) AND (end_date >= (CURRENT_DATE)::timestamp without time zone) AND (works_state = ANY ('{0,2,3,4,5,6,7,8}'::integer[])))
9. 0.393 0.788 ↑ 1.0 1,309 1

Hash (cost=36.09..36.09 rows=1,309 width=26) (actual time=0.788..0.788 rows=1,309 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
10. 0.395 0.395 ↑ 1.0 1,309 1

Seq Scan on orgref publisher (cost=0.00..36.09 rows=1,309 width=26) (actual time=0.002..0.395 rows=1,309 loops=1)

11. 0.416 0.851 ↑ 1.0 1,309 1

Hash (cost=36.09..36.09 rows=1,309 width=26) (actual time=0.851..0.851 rows=1,309 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
12. 0.435 0.435 ↑ 1.0 1,309 1

Seq Scan on orgref resporg (cost=0.00..36.09 rows=1,309 width=26) (actual time=0.002..0.435 rows=1,309 loops=1)

13. 0.005 0.011 ↑ 68.0 5 1

Hash (cost=13.40..13.40 rows=340 width=92) (actual time=0.011..0.011 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.006 0.006 ↑ 68.0 5 1

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

15. 0.002 821.571 ↓ 0.0 0 1

Nested Loop Left Join (cost=15.76..63,679.11 rows=1 width=654) (actual time=821.570..821.571 rows=0 loops=1)

  • Join Filter: (d_1.publisher_organisation_id = oef.organisation_id)
16. 0.001 821.569 ↓ 0.0 0 1

Nested Loop (cost=15.76..63,614.24 rows=1 width=443) (actual time=821.569..821.569 rows=0 loops=1)

17. 0.002 821.568 ↓ 0.0 0 1

Nested Loop (cost=15.61..63,606.02 rows=1 width=355) (actual time=821.568..821.568 rows=0 loops=1)

  • Join Filter: (d_1.responsible_org_orgref = resporg_1.swa_org_ref)
18. 0.001 821.566 ↓ 0.0 0 1

Nested Loop (cost=15.61..63,553.57 rows=1 width=333) (actual time=821.566..821.566 rows=0 loops=1)

  • Join Filter: (d_1.publisher_orgref = publisher_1.swa_org_ref)
19. 0.029 821.565 ↓ 0.0 0 1

Nested Loop (cost=15.61..63,501.12 rows=1 width=311) (actual time=821.565..821.565 rows=0 loops=1)

20. 0.036 0.036 ↓ 31.0 31 1

Seq Scan on entity_category ecat (cost=0.00..10.50 rows=1 width=118) (actual time=0.010..0.036 rows=31 loops=1)

  • Filter: ((entitygroupid)::text = 'RW'::text)
  • Rows Removed by Filter: 61
21. 821.500 821.500 ↓ 0.0 0 31

Index Scan using idx_entity_category on entity d_1 (cost=15.61..63,490.61 rows=1 width=319) (actual time=26.500..26.500 rows=0 loops=31)

  • Index Cond: ((entity_category)::text = (ecat.nsa_code)::text)
  • Filter: (active AND (tm__entity_id_sw_xref IS NULL) AND (entity_type > 1) AND (location_wgs84 && '0103000020E6100000010000000500000055F4EFFFBFD9CABF62E00BEA08C8494055F4EFFFBFD9CABFF5031BA612C9494072F1F0FF3F2EC9BFF5031BA612C9494072F1F0FF3F2EC9BF62E00BEA08C8494055F4EFFFBFD9CABF62E00BEA08C84940'::geometry) AND (published = 1) AND (end_date >= LOCALTIMESTAMP) AND (LOCALTIMESTAMP > publish_date) AND (end_date >= (CURRENT_DATE)::timestamp without time zone) AND (((hashed SubPlan 1) AND (entity_type = 2)) OR (entity_type > 2)) AND (start_date <= ((CURRENT_DATE + '1 day'::interval) - '00:00:01'::interval)))
  • Rows Removed by Filter: 25432
22.          

SubPlan (forIndex Scan)

23. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on organisation_extended_funcs (cost=4.37..15.04 rows=11 width=4) (never executed)

  • Recheck Cond: (extended_function_id = 14)
24. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on organisation_extended_funcs_pkey (cost=0.00..4.36 rows=11 width=0) (never executed)

  • Index Cond: (extended_function_id = 14)
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on orgref publisher_1 (cost=0.00..36.09 rows=1,309 width=26) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Seq Scan on orgref resporg_1 (cost=0.00..36.09 rows=1,309 width=26) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using eton_impact_pkey on eton_impact ei_1 (cost=0.15..8.17 rows=1 width=92) (never executed)

  • Index Cond: (impact_score = d_1.impact_score)
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on organisation_extended_funcs oef (cost=0.00..32.60 rows=2,260 width=8) (never executed)