explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rlsx

Settings
# exclusive inclusive rows x rows loops node
1. 1.174 2.690 ↓ 32.0 32 1

Nested Loop (cost=9.24..10.62 rows=1 width=2,309) (actual time=0.906..2.690 rows=32 loops=1)

  • Join Filter: (ex.id = elf.expenseid)
  • Buffers: shared hit=90
2.          

CTE projectclientallocation

3. 0.164 0.365 ↑ 1.0 25 1

Hash Right Join (cost=2.56..3.94 rows=25 width=52) (actual time=0.174..0.365 rows=25 loops=1)

  • Hash Cond: (pc.projectid = pj.id)
  • Buffers: shared hit=3
4. 0.054 0.054 ↑ 1.0 24 1

Seq Scan on projectclient pc (cost=0.00..1.24 rows=24 width=36) (actual time=0.006..0.054 rows=24 loops=1)

  • Buffers: shared hit=1
5. 0.065 0.147 ↑ 1.0 25 1

Hash (cost=2.25..2.25 rows=25 width=8) (actual time=0.146..0.147 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
6. 0.082 0.082 ↑ 1.0 25 1

Seq Scan on project pj (cost=0.00..2.25 rows=25 width=8) (actual time=0.008..0.082 rows=25 loops=1)

  • Buffers: shared hit=2
7.          

Initplan (forNested Loop)

8. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on systeminformation (cost=0.00..1.01 rows=1 width=4) (actual time=0.006..0.010 rows=1 loops=1)

  • Buffers: shared hit=1
9. 0.225 1.346 ↓ 32.0 32 1

Hash Right Join (cost=4.16..4.76 rows=1 width=1,497) (actual time=0.852..1.346 rows=32 loops=1)

  • Hash Cond: (pca.projectid = expenseentry.projectid)
  • Join Filter: ((expenseentry.entrydate >= pca.effectivedate) AND (expenseentry.entrydate <= pca.enddate))
  • Buffers: shared hit=26
10. 0.488 0.488 ↑ 1.0 25 1

CTE Scan on projectclientallocation pca (cost=0.00..0.50 rows=25 width=48) (actual time=0.180..0.488 rows=25 loops=1)

  • Buffers: shared hit=3
11. 0.112 0.633 ↓ 32.0 32 1

Hash (cost=4.14..4.14 rows=1 width=1,457) (actual time=0.632..0.633 rows=32 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=23
12. 0.209 0.521 ↓ 32.0 32 1

Nested Loop (cost=1.76..4.14 rows=1 width=1,457) (actual time=0.125..0.521 rows=32 loops=1)

  • Buffers: shared hit=23
13. 0.069 0.172 ↓ 10.0 10 1

Hash Join (cost=1.62..2.78 rows=1 width=703) (actual time=0.101..0.172 rows=10 loops=1)

  • Hash Cond: (ex.reimbursementcurrencyid = exchangerate.fixedcurrencyid)
  • Join Filter: ((ex.expensedate >= exchangerate.effectivedate) AND (ex.expensedate <= exchangerate.enddate))
  • Buffers: shared hit=3
14. 0.032 0.032 ↑ 1.0 10 1

Seq Scan on expense ex (cost=0.00..1.10 rows=10 width=683) (actual time=0.011..0.032 rows=10 loops=1)

  • Buffers: shared hit=1
15. 0.023 0.071 ↓ 7.0 7 1

Hash (cost=1.61..1.61 rows=1 width=32) (actual time=0.070..0.071 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
16. 0.048 0.048 ↓ 7.0 7 1

Seq Scan on exchangerate (cost=0.00..1.61 rows=1 width=32) (actual time=0.021..0.048 rows=7 loops=1)

  • Filter: (variablecurrencyid = $1)
  • Rows Removed by Filter: 42
  • Buffers: shared hit=2
17. 0.140 0.140 ↓ 3.0 3 10

Index Scan using ixeeexpenseid on expenseentry (cost=0.14..1.35 rows=1 width=754) (actual time=0.007..0.014 rows=3 loops=10)

  • Index Cond: (expenseid = ex.id)
  • Buffers: shared hit=20
18. 0.160 0.160 ↑ 1.0 1 32

Index Scan using dm_expenselist_facts_pkey on dm_expenselist_facts elf (cost=0.14..0.53 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=32)

  • Index Cond: (expenseid = expenseentry.expenseid)
  • Buffers: shared hit=64
Planning time : 3.569 ms
Execution time : 3.421 ms