explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ndCL

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 122.139 ↓ 0.0 0 1

Nested Loop Semi Join (cost=2.15..299.98 rows=1 width=5) (actual time=122.139..122.139 rows=0 loops=1)

  • Join Filter: (t0.agid = t4.id)
  • Rows Removed by Join Filter: 20
  • Buffers: shared hit=121
2. 0.018 0.018 ↑ 4.5 4 1

Seq Scan on entity_group_map t0 estimated (cost=0.00..1.54 rows=18 width=10) (actual time=0.014..0.018 rows=4 loops=1)

  • Filter: (entity_type = ($1)::numeric)
  • Rows Removed by Filter: 32
  • Buffers: shared hit=1
3. 0.005 122.112 ↓ 5.0 5 4

Materialize (cost=2.15..298.17 rows=1 width=30) (actual time=0.020..30.528 rows=5 loops=4)

  • Buffers: shared hit=120
4. 0.007 122.107 ↓ 5.0 5 1

Nested Loop (cost=2.15..298.17 rows=1 width=30) (actual time=0.078..122.107 rows=5 loops=1)

  • Join Filter: (t1.bodyid = t2.id)
  • Buffers: shared hit=120
5. 0.015 122.080 ↓ 5.0 5 1

Nested Loop (cost=1.87..297.80 rows=1 width=55) (actual time=0.068..122.080 rows=5 loops=1)

  • Join Filter: (t4.id = t3.id)
  • Rows Removed by Join Filter: 32
  • Buffers: shared hit=104
6. 0.925 122.060 ↓ 5.0 5 1

Nested Loop (cost=1.87..296.22 rows=1 width=50) (actual time=0.065..122.060 rows=5 loops=1)

  • Join Filter: (wagm.agentid = t1.bodyid)
  • Rows Removed by Join Filter: 4,006
  • Buffers: shared hit=99
7. 0.015 0.015 ↑ 1.0 1 1

Index Scan using ixagrms on agent_ref t1 (cost=0.28..8.30 rows=1 width=5) (actual time=0.013..0.015 rows=1 loops=1)

  • Index Cond: (((reference)::text = ($3)::text) AND (deleted = ($4)::numeric))
  • Buffers: shared hit=3
8. 35.162 121.120 ↓ 28.6 4,011 1

Hash Join (cost=1.58..286.16 rows=140 width=45) (actual time=0.024..121.120 rows=4,011 loops=1)

  • Hash Cond: (wagm.agid = t4.id)
  • Buffers: shared hit=96
9. 33.083 85.943 ↓ 3.7 4,011 1

Append (cost=0.00..270.91 rows=1,078 width=72) (actual time=0.002..85.943 rows=4,011 loops=1)

  • Buffers: shared hit=95
10. 0.005 0.005 ↑ 1.0 31 1

Seq Scan on writable_agent_group_map wagm (cost=0.00..1.31 rows=31 width=15) (actual time=0.002..0.005 rows=31 loops=1)

  • Buffers: shared hit=1
11. 1.175 52.855 ↓ 3.8 3,980 1

Subquery Scan on *SELECT* 2 (cost=1.52..269.29 rows=1,047 width=42) (actual time=0.023..52.855 rows=3,980 loops=1)

  • Buffers: shared hit=94
12. 51.226 51.680 ↓ 3.8 3,980 1

Hash Join (cost=1.52..256.20 rows=1,047 width=14) (actual time=0.022..51.680 rows=3,980 loops=1)

  • Hash Cond: (ab.tenant_id = ag.tenant_id)
  • Join Filter: (((ab.agent_category)::text = (ag.agent_category)::text) OR (ag.agent_category IS NULL))
  • Rows Removed by Join Filter: 15,920
  • Buffers: shared hit=94
13. 0.441 0.441 ↑ 1.0 1,990 1

Seq Scan on agent_body ab (cost=0.00..112.90 rows=1,990 width=19) (actual time=0.003..0.441 rows=1,990 loops=1)

  • Buffers: shared hit=93
14. 0.005 0.013 ↓ 2.0 10 1

Hash (cost=1.46..1.46 rows=5 width=15) (actual time=0.013..0.013 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
15. 0.008 0.008 ↓ 2.0 10 1

Seq Scan on agent_group ag (cost=0.00..1.46 rows=5 width=15) (actual time=0.002..0.008 rows=10 loops=1)

  • Filter: ((type = 1) AND ((id > '999'::numeric) OR (id = '1'::numeric)))
  • Rows Removed by Filter: 16
  • Buffers: shared hit=1
16. 0.009 0.015 ↑ 1.0 26 1

Hash (cost=1.26..1.26 rows=26 width=5) (actual time=0.015..0.015 rows=26 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
17. 0.006 0.006 ↑ 1.0 26 1

Seq Scan on agent_group t4 (cost=0.00..1.26 rows=26 width=5) (actual time=0.002..0.006 rows=26 loops=1)

  • Buffers: shared hit=1
18. 0.005 0.005 ↑ 3.7 7 5

Seq Scan on agent_group t3 (cost=0.00..1.26 rows=26 width=5) (actual time=0.001..0.001 rows=7 loops=5)

  • Buffers: shared hit=5
19. 0.020 0.020 ↑ 1.0 1 5

Index Only Scan using pk_agent_body on agent_body t2 (cost=0.28..0.35 rows=1 width=5) (actual time=0.004..0.004 rows=1 loops=5)

  • Index Cond: (id = wagm.agentid)
  • Heap Fetches: 5
  • Buffers: shared hit=16