explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e7fsz

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 59.932 1,296.500 ↑ 2.0 200,000 1

Unique (cost=7,498,141.49..7,507,961.09 rows=392,784 width=161) (actual time=1,193.971..1,296.500 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. 128.630 1,236.568 ↑ 2.0 200,000 1

Sort (cost=7,498,141.49..7,499,123.45 rows=392,784 width=161) (actual time=1,193.970..1,236.568 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: 30032kB
3. 14.187 1,107.938 ↑ 2.0 200,000 1

Append (cost=0.00..7,397,203.22 rows=392,784 width=161) (actual time=58.224..1,107.938 rows=200,000 loops=1)

4. 69.930 69.930 ↓ 1.0 100,000 1

Seq Scan on public.addr a (cost=0.00..25,204.03 rows=98,967 width=184) (actual time=58.222..69.930 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. 15.885 1,023.821 ↑ 2.9 100,000 1

Subquery Scan on *SELECT* 2 (cost=33,896.07..7,369,045.60 rows=293,817 width=225) (actual time=308.396..1,023.821 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. 540.430 1,007.936 ↑ 2.9 100,000 1

Hash Anti Join (cost=33,896.07..7,366,107.43 rows=293,817 width=225) (actual time=308.394..1,007.936 rows=100,000 loops=1)

  • Output: a2.id, a2.ficomp_id, a2.usr_id, a2.wallet_id, (SubPlan 1), (SubPlan 3), 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. 118.559 118.559 ↑ 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..118.559 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.961 148.947 ↑ 1.2 400,003 1

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

  • Output: a3.usr_id, a3.id
  • Buckets: 131072 Batches: 8 Memory Usage: 3355kB
9. 89.986 89.986 ↑ 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..89.986 rows=500,003 loops=1)

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

SubPlan (for Hash Anti Join)

11. 200.000 200.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.002..0.002 rows=1 loops=100,000)

  • Output: u.full_name
  • Index Cond: (u.id = a2.usr_id)
12. 0.000 0.000 ↓ 0.0 0 100,000

Index Scan using ficomp_pkey on public.ficomp f (cost=8.45..16.47 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=100,000)

  • Output: f.title
  • Index Cond: (f.id = $2)
13.          

Initplan (for Index Scan)

14. 100.000 100.000 ↑ 1.0 1 100,000

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

  • Output: u_1.ficomp_id
  • Index Cond: (u_1.id = a2.usr_id)
Planning time : 0.993 ms
Execution time : 1,311.383 ms