explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CS7A

Settings
# exclusive inclusive rows x rows loops node
1. 0.343 36.731 ↑ 2.7 671 1

Limit (cost=6,296.05..14,469.06 rows=1,801 width=472) (actual time=10.344..36.731 rows=671 loops=1)

2. 23.031 36.388 ↑ 3.3 671 1

Hash Join (cost=6,296.05..16,406.80 rows=2,228 width=472) (actual time=10.343..36.388 rows=671 loops=1)

  • Hash Cond: (d.publisher_orgref = publisher.swa_org_ref)
3. 0.465 12.511 ↑ 3.3 671 1

Hash Join (cost=6,242.57..14,055.70 rows=2,228 width=619) (actual time=9.338..12.511 rows=671 loops=1)

  • Hash Cond: (d.impact_score = ei.impact_score)
4. 0.791 12.039 ↑ 3.3 671 1

Nested Loop Left Join (cost=6,241.46..14,023.95 rows=2,228 width=531) (actual time=9.326..12.039 rows=671 loops=1)

  • Join Filter: (d.impact_score = ei_lang.impact_score)
5. 0.654 11.248 ↑ 3.3 671 1

Hash Join (cost=6,241.46..13,989.47 rows=2,228 width=313) (actual time=9.316..11.248 rows=671 loops=1)

  • Hash Cond: (d.responsible_org_orgref = resporg.swa_org_ref)
6. 1.349 9.797 ↑ 3.3 671 1

Bitmap Heap Scan on entity_agg d (cost=6,187.98..13,905.35 rows=2,230 width=291) (actual time=8.511..9.797 rows=671 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('16/11/2018 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('15/11/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
  • Filter: ((entity_type = 1) AND (impact_score = ANY ('{1,2,3,4}'::integer[])))
  • Rows Removed by Filter: 50
  • Heap Blocks: exact=510
7. 8.448 8.448 ↑ 3.7 721 1

Bitmap Index Scan on idx_entity_agg_coords_dates_impact (cost=0.00..6,187.43 rows=2,651 width=0) (actual time=8.448..8.448 rows=721 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('16/11/2018 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('15/11/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
8. 0.398 0.797 ↑ 1.0 1,310 1

Hash (cost=37.10..37.10 rows=1,310 width=26) (actual time=0.797..0.797 rows=1,310 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
9. 0.399 0.399 ↑ 1.0 1,310 1

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

10. 0.000 0.000 ↓ 0.0 0 671

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

11. 0.007 0.007 ↓ 0.0 0 1

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

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 5
12. 0.002 0.007 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.005 0.005 ↑ 1.0 5 1

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

14. 0.383 0.846 ↑ 1.0 1,310 1

Hash (cost=37.10..37.10 rows=1,310 width=26) (actual time=0.846..0.846 rows=1,310 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
15. 0.463 0.463 ↑ 1.0 1,310 1

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