explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1twM

Settings
# exclusive inclusive rows x rows loops node
1. 0.051 30.599 ↑ 19.4 57 1

Hash Left Join (cost=11,350.16..51,970.64 rows=1,106 width=850) (actual time=8.803..30.599 rows=57 loops=1)

  • Hash Cond: (d.publisher_organisation_id = live_orgs.organisation_id)
2.          

CTE live_orgs

3. 0.062 0.062 ↓ 47.5 95 1

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs (cost=0.27..4.31 rows=2 width=4) (actual time=0.010..0.062 rows=95 loops=1)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 95
4. 0.065 30.400 ↑ 19.4 57 1

Nested Loop (cost=11,345.79..51,953.73 rows=1,106 width=899) (actual time=8.647..30.400 rows=57 loops=1)

5. 0.023 0.023 ↓ 24.0 24 1

Seq Scan on entity_category ec (cost=0.00..3.14 rows=1 width=852) (actual time=0.008..0.023 rows=24 loops=1)

  • Filter: ((entitygroupid)::text = 'INC'::text)
  • Rows Removed by Filter: 68
6. 25.512 30.312 ↑ 898.5 2 24

Bitmap Heap Scan on entity d (cost=11,345.79..51,932.62 rows=1,797 width=165) (actual time=0.642..1.263 rows=2 loops=24)

  • Recheck Cond: ((entity_category)::text = (ec.nsa_code)::text)
  • Filter: (active AND (published = 1) AND (end_date >= LOCALTIMESTAMP) AND (publish_date < LOCALTIMESTAMP))
  • Rows Removed by Filter: 1298
  • Heap Blocks: exact=21726
7. 4.800 4.800 ↑ 8.1 1,331 24

Bitmap Index Scan on idx_entity_category (cost=0.00..11,345.34 rows=10,772 width=0) (actual time=0.200..0.200 rows=1,331 loops=24)

  • Index Cond: ((entity_category)::text = (ec.nsa_code)::text)
8. 0.029 0.148 ↓ 47.5 95 1

Hash (cost=0.04..0.04 rows=2 width=4) (actual time=0.148..0.148 rows=95 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
9. 0.119 0.119 ↓ 47.5 95 1

CTE Scan on live_orgs (cost=0.00..0.04 rows=2 width=4) (actual time=0.012..0.119 rows=95 loops=1)