explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gl5g

Settings
# exclusive inclusive rows x rows loops node
1. 9.547 2,744.587 ↑ 1.7 17,762 1

Append (cost=484.17..196,883.86 rows=30,472 width=1,014) (actual time=5.434..2,744.587 rows=17,762 loops=1)

2. 8.631 992.496 ↑ 1.4 8,835 1

Subquery Scan on *SELECT* 1 (cost=484.17..57,114.03 rows=12,157 width=688) (actual time=5.433..992.496 rows=8,835 loops=1)

3. 922.457 983.865 ↑ 1.4 8,835 1

Hash Join (cost=484.17..56,931.68 rows=12,157 width=684) (actual time=5.432..983.865 rows=8,835 loops=1)

  • Hash Cond: (d.impact_score = ei.impact_score)
4. 7.674 61.397 ↑ 1.4 8,835 1

Hash Join (cost=466.52..41,290.36 rows=12,157 width=405) (actual time=4.816..61.397 rows=8,835 loops=1)

  • Hash Cond: (d.responsible_org_orgref = resporg.swa_org_ref)
5. 10.571 52.895 ↑ 1.4 8,835 1

Hash Join (cost=414.07..41,205.90 rows=12,157 width=383) (actual time=3.982..52.895 rows=8,835 loops=1)

  • Hash Cond: (d.publisher_orgref = publisher.swa_org_ref)
6. 39.574 41.536 ↑ 1.4 8,835 1

Bitmap Heap Scan on entity_eton_agg d (cost=361.62..41,121.43 rows=12,157 width=361) (actual time=3.187..41.536 rows=8,835 loops=1)

  • Recheck Cond: (works_state = ANY ('{0,2,3,4,5,6,7,8}'::integer[]))
  • Filter: (active AND (end_date >= LOCALTIMESTAMP) AND (end_date >= (CURRENT_DATE)::timestamp without time zone) AND (start_date <= ((CURRENT_DATE + '1 day'::interval) - '00:00:01'::interval)))
  • Rows Removed by Filter: 12199
  • Heap Blocks: exact=8878
7. 1.962 1.962 ↑ 1.0 21,034 1

Bitmap Index Scan on idx_entity_eton_agg_works_state (cost=0.00..358.58 rows=21,368 width=0) (actual time=1.962..1.962 rows=21,034 loops=1)

  • Index Cond: (works_state = ANY ('{0,2,3,4,5,6,7,8}'::integer[]))
8. 0.394 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
9. 0.394 0.394 ↑ 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.394 rows=1,309 loops=1)

10. 0.395 0.828 ↑ 1.0 1,309 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
11. 0.433 0.433 ↑ 1.0 1,309 1

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

12. 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
13. 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)

14. 882.388 1,742.544 ↑ 2.1 8,927 1

Hash Left Join (cost=199.02..139,586.68 rows=18,315 width=637) (actual time=2.186..1,742.544 rows=8,927 loops=1)

  • Hash Cond: (d_1.publisher_organisation_id = oef.organisation_id)
15. 6.493 859.921 ↓ 4.9 7,986 1

Hash Join (cost=138.17..65,208.30 rows=1,622 width=425) (actual time=1.796..859.921 rows=7,986 loops=1)

  • Hash Cond: (d_1.impact_score = ei_1.impact_score)
16. 6.890 853.416 ↓ 4.9 7,986 1

Hash Join (cost=120.52..65,186.33 rows=1,622 width=337) (actual time=1.775..853.416 rows=7,986 loops=1)

  • Hash Cond: (d_1.responsible_org_orgref = resporg_1.swa_org_ref)
17. 7.914 845.671 ↓ 4.9 7,987 1

Hash Join (cost=68.07..65,129.61 rows=1,622 width=315) (actual time=0.914..845.671 rows=7,987 loops=1)

  • Hash Cond: (d_1.publisher_orgref = publisher_1.swa_org_ref)
18. 5.516 836.970 ↓ 4.9 7,987 1

Nested Loop (cost=15.61..65,072.89 rows=1,622 width=293) (actual time=0.118..836.970 rows=7,987 loops=1)

19. 0.034 0.034 ↓ 31.0 31 1

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

  • Filter: ((entitygroupid)::text = 'RW'::text)
  • Rows Removed by Filter: 61
20. 831.377 831.420 ↑ 6.3 258 31

Index Scan using idx_entity_category on entity d_1 (cost=15.61..65,046.17 rows=1,622 width=301) (actual time=0.566..26.820 rows=258 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 (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: 24915
21.          

SubPlan (forIndex Scan)

22. 0.033 0.043 ↓ 8.5 93 1

Bitmap Heap Scan on organisation_extended_funcs (cost=4.37..15.04 rows=11 width=4) (actual time=0.017..0.043 rows=93 loops=1)

  • Recheck Cond: (extended_function_id = 14)
  • Heap Blocks: exact=3
23. 0.010 0.010 ↓ 8.5 93 1

Bitmap Index Scan on organisation_extended_funcs_pkey (cost=0.00..4.36 rows=11 width=0) (actual time=0.010..0.010 rows=93 loops=1)

  • Index Cond: (extended_function_id = 14)
24. 0.390 0.787 ↑ 1.0 1,309 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
25. 0.397 0.397 ↑ 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.397 rows=1,309 loops=1)

26. 0.401 0.855 ↑ 1.0 1,309 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
27. 0.454 0.454 ↑ 1.0 1,309 1

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

28. 0.004 0.012 ↑ 68.0 5 1

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

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

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

30. 0.122 0.235 ↑ 5.2 437 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 50kB
31. 0.113 0.113 ↑ 5.2 437 1

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