explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kGwPm : Optimization for: plan #e7fsz

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 34.425 936.889 ↑ 2.0 200,000 1

Unique (cost=5,056,522.22..5,066,341.82 rows=392,784 width=161) (actual time=877.408..936.889 rows=200,000 loops=1)

  • Output: a.id, a.ficomp_id, a.usr_id, a.wallet_id, a.name, a.firm_name, a.notes, a.addr, a.is_active
2. 89.723 902.464 ↑ 2.0 200,000 1

Sort (cost=5,056,522.22..5,057,504.18 rows=392,784 width=161) (actual time=877.406..902.464 rows=200,000 loops=1)

  • Output: a.id, a.ficomp_id, a.usr_id, a.wallet_id, a.name, a.firm_name, a.notes, a.addr, a.is_active
  • Sort Key: a.id, a.ficomp_id, a.usr_id, a.wallet_id, a.name, a.firm_name, a.notes, a.addr, a.is_active
  • Sort Method: external merge Disk: 30440kB
3. 12.637 812.741 ↑ 2.0 200,000 1

Append (cost=0.00..4,955,583.95 rows=392,784 width=161) (actual time=91.744..812.741 rows=200,000 loops=1)

4. 103.618 103.618 ↓ 1.0 100,000 1

Seq Scan on public.addr a (cost=0.00..25,204.03 rows=98,967 width=184) (actual time=91.743..103.618 rows=100,000 loops=1)

  • Output: a.id, a.ficomp_id, a.usr_id, a.wallet_id, a.name, a.firm_name, a.notes, a.addr, a.is_active
  • Filter: (a.usr_id IS NULL)
  • Rows Removed by Filter: 400003
5. 11.893 696.486 ↑ 2.9 100,000 1

Subquery Scan on *SELECT* 2 (cost=33,896.07..4,927,426.33 rows=293,817 width=225) (actual time=288.410..696.486 rows=100,000 loops=1)

  • Output: "*SELECT* 2".id, "*SELECT* 2".ficomp_id, "*SELECT* 2".usr_id, "*SELECT* 2".wallet_id, "*SELECT* 2".name, "*SELECT* 2".firm_name, "*SELECT* 2".notes, "*SELECT* 2".addrs, "*SELECT* 2".is_active
6. 245.490 684.593 ↑ 2.9 100,000 1

Hash Anti Join (cost=33,896.07..4,924,488.16 rows=293,817 width=225) (actual time=288.408..684.593 rows=100,000 loops=1)

  • Output: a2.id, a2.ficomp_id, a2.usr_id, a2.wallet_id, (SubPlan 1), (SubPlan 2), a2.notes, a2.addr, a2.is_active
  • Hash Cond: (a2.usr_id = a3.usr_id)
  • Join Filter: (a2.id < a3.id)
  • Rows Removed by Join Filter: 400003
7. 93.155 93.155 ↑ 1.0 400,003 1

Seq Scan on public.addr a2 (cost=0.00..25,204.03 rows=401,036 width=161) (actual time=0.007..93.155 rows=400,003 loops=1)

  • Output: a2.id, a2.ficomp_id, a2.usr_id, a2.wallet_id, a2.notes, a2.addr, a2.is_active
  • Filter: (a2.usr_id IS NOT NULL)
  • Rows Removed by Filter: 100000
8. 58.269 145.948 ↑ 1.2 400,003 1

Hash (cost=25,204.03..25,204.03 rows=500,003 width=16) (actual time=145.948..145.948 rows=400,003 loops=1)

  • Output: a3.usr_id, a3.id
  • Buckets: 131072 Batches: 8 Memory Usage: 3355kB
9. 87.679 87.679 ↑ 1.0 500,003 1

Seq Scan on public.addr a3 (cost=0.00..25,204.03 rows=500,003 width=16) (actual time=0.001..87.679 rows=500,003 loops=1)

  • Output: a3.usr_id, a3.id
10.          

SubPlan (for Hash Anti Join)

11. 100.000 100.000 ↑ 1.0 1 100,000

Index Scan using usr_pkey on public.usr u (cost=0.29..8.31 rows=1 width=9) (actual time=0.001..0.001 rows=1 loops=100,000)

  • Output: u.full_name
  • Index Cond: (u.id = a2.usr_id)
12. 100.000 100.000 ↑ 1.0 1 100,000

Index Scan using ficomp_pkey on public.ficomp f (cost=0.14..8.16 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=100,000)

  • Output: f.title
  • Index Cond: (f.id = a2.ficomp_id)
Planning time : 0.484 ms
Execution time : 945.744 ms