explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vtAn

Settings
# exclusive inclusive rows x rows loops node
1. 158.645 71,438.216 ↓ 3.8 257,661 1

Append (cost=122.56..332,589.19 rows=67,472 width=1,014) (actual time=57.213..71,438.216 rows=257,661 loops=1)

2. 21.614 8,848.862 ↑ 1.0 20,991 1

Subquery Scan on *SELECT* 1 (cost=122.56..80,310.41 rows=21,077 width=688) (actual time=57.212..8,848.862 rows=20,991 loops=1)

3. 2,310.775 8,827.248 ↑ 1.0 20,991 1

Hash Join (cost=122.56..79,994.25 rows=21,077 width=684) (actual time=57.209..8,827.248 rows=20,991 loops=1)

  • Hash Cond: (d.impact_score = ei.impact_score)
4. 18.730 6,516.459 ↑ 1.0 20,991 1

Hash Join (cost=104.91..52,889.34 rows=21,077 width=405) (actual time=5.901..6,516.459 rows=20,991 loops=1)

  • Hash Cond: (d.responsible_org_orgref = resporg.swa_org_ref)
5. 26.827 6,493.378 ↑ 1.0 20,991 1

Hash Join (cost=52.45..52,781.38 rows=21,077 width=383) (actual time=1.537..6,493.378 rows=20,991 loops=1)

  • Hash Cond: (d.publisher_orgref = publisher.swa_org_ref)
6. 6,465.509 6,465.509 ↑ 1.0 20,991 1

Seq Scan on entity_eton_agg d (cost=0.00..52,673.42 rows=21,077 width=361) (actual time=0.469..6,465.509 rows=20,991 loops=1)

  • Filter: (active AND (works_state = ANY ('{0,2,3,4,5,6,7,8}'::integer[])))
  • Rows Removed by Filter: 13792
7. 0.568 1.042 ↑ 1.0 1,309 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
8. 0.474 0.474 ↑ 1.0 1,309 1

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

9. 0.553 4.351 ↑ 1.0 1,309 1

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

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

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

11. 0.006 0.014 ↑ 68.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.008 0.008 ↑ 68.0 5 1

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

13. 24,271.607 62,430.709 ↓ 5.1 236,670 1

Hash Left Join (cost=183.96..251,814.83 rows=46,395 width=639) (actual time=9.405..62,430.709 rows=236,670 loops=1)

  • Hash Cond: (d_1.publisher_organisation_id = oef.organisation_id)
14. 92.110 38,151.748 ↓ 23.8 101,242 1

Hash Join (cost=123.11..63,454.04 rows=4,253 width=429) (actual time=1.766..38,151.748 rows=101,242 loops=1)

  • Hash Cond: (d_1.impact_score = ei_1.impact_score)
15. 89.977 38,059.627 ↓ 23.8 101,243 1

Hash Join (cost=105.45..63,425.09 rows=4,253 width=341) (actual time=1.740..38,059.627 rows=101,243 loops=1)

  • Hash Cond: (d_1.responsible_org_orgref = resporg_1.swa_org_ref)
16. 118.739 37,968.801 ↓ 23.8 101,260 1

Hash Join (cost=53.00..63,361.44 rows=4,253 width=319) (actual time=0.879..37,968.801 rows=101,260 loops=1)

  • Hash Cond: (d_1.publisher_orgref = publisher_1.swa_org_ref)
17. 79.336 37,849.254 ↓ 23.9 101,533 1

Nested Loop (cost=0.55..63,297.79 rows=4,253 width=297) (actual time=0.050..37,849.254 rows=101,533 loops=1)

18. 0.696 0.696 ↓ 31.0 31 1

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

  • Filter: ((entitygroupid)::text = 'RW'::text)
  • Rows Removed by Filter: 61
19. 37,769.222 37,769.222 ↑ 1.3 3,275 31

Index Scan using idx_entity_category on entity d_1 (cost=0.55..63,244.76 rows=4,253 width=305) (actual time=2.167..1,218.362 rows=3,275 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))
  • Rows Removed by Filter: 21889
20. 0.413 0.808 ↑ 1.0 1,309 1

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

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

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

22. 0.414 0.849 ↑ 1.0 1,309 1

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

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

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

24. 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
25. 0.006 0.006 ↑ 68.0 5 1

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

26. 0.131 7.354 ↑ 5.2 437 1

Hash (cost=32.60..32.60 rows=2,260 width=8) (actual time=7.354..7.354 rows=437 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 50kB
27. 7.223 7.223 ↑ 5.2 437 1

Seq Scan on organisation_extended_funcs oef (cost=0.00..32.60 rows=2,260 width=8) (actual time=7.074..7.223 rows=437 loops=1)