explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ej8G

Settings
# exclusive inclusive rows x rows loops node
1. 885.295 1,795.337 ↑ 1.8 8,852 1

Hash Left Join (cost=199.02..122,615.70 rows=15,657 width=643) (actual time=2.263..1,795.337 rows=8,852 loops=1)

  • Hash Cond: (d.publisher_organisation_id = oef.organisation_id)
2. 6.273 909.805 ↓ 5.6 7,911 1

Hash Join (cost=138.17..59,016.97 rows=1,407 width=431) (actual time=1.736..909.805 rows=7,911 loops=1)

  • Hash Cond: (d.impact_score = ei.impact_score)
3. 6.769 903.521 ↓ 5.6 7,911 1

Hash Join (cost=120.52..58,995.58 rows=1,407 width=343) (actual time=1.720..903.521 rows=7,911 loops=1)

  • Hash Cond: (d.responsible_org_orgref = resporg.swa_org_ref)
4. 7.813 895.932 ↓ 5.6 7,912 1

Hash Join (cost=68.07..58,939.42 rows=1,407 width=321) (actual time=0.894..895.932 rows=7,912 loops=1)

  • Hash Cond: (d.publisher_orgref = publisher.swa_org_ref)
5. 5.478 887.337 ↓ 5.6 7,912 1

Nested Loop (cost=15.61..58,883.27 rows=1,407 width=299) (actual time=0.106..887.337 rows=7,912 loops=1)

6. 0.033 0.033 ↓ 31.0 31 1

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

  • Filter: ((entitygroupid)::text = 'RW'::text)
  • Rows Removed by Filter: 61
7. 881.785 881.826 ↑ 5.5 255 31

Index Scan using idx_entity_category on entity d (cost=15.61..58,858.70 rows=1,407 width=307) (actual time=0.582..28.446 rows=255 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: 24953
8.          

SubPlan (forIndex Scan)

9. 0.032 0.041 ↓ 8.5 93 1

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

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

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

  • Index Cond: (extended_function_id = 14)
11. 0.386 0.782 ↑ 1.0 1,309 1

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

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

13. 0.391 0.820 ↑ 1.0 1,309 1

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

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

15. 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
16. 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)

17. 0.124 0.237 ↑ 5.2 437 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 50kB
18. 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)