explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YWHJ

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.008 774.819 ↑ 1.0 40 1

Limit (cost=5,056,522.22..5,056,523.22 rows=40 width=161) (actual time=774.797..774.819 rows=40 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. 0.012 774.811 ↑ 9,819.6 40 1

Unique (cost=5,056,522.22..5,066,341.82 rows=392,784 width=161) (actual time=774.795..774.811 rows=40 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
3. 59.671 774.799 ↑ 9,819.6 40 1

Sort (cost=5,056,522.22..5,057,504.18 rows=392,784 width=161) (actual time=774.795..774.799 rows=40 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
4. 11.024 715.128 ↑ 2.0 200,000 1

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

5. 80.733 80.733 ↓ 1.0 100,000 1

Seq Scan on public.addr a (cost=0.00..25,204.03 rows=98,967 width=184) (actual time=69.191..80.733 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
6. 10.533 623.371 ↑ 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=262.423..623.371 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".addr, "*SELECT* 2".is_active
7. 203.857 612.838 ↑ 2.9 100,000 1

Hash Anti Join (cost=33,896.07..4,924,488.16 rows=293,817 width=225) (actual time=262.422..612.838 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
8. 93.103 93.103 ↑ 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.005..93.103 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
9. 48.344 115.878 ↑ 1.2 400,003 1

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

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

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

SubPlan (for Hash Anti Join)

12. 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)
13. 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.000..0.001 rows=1 loops=100,000)

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