explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bdh

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.009 2,673.296 ↑ 1.0 10 1

Limit (cost=791,011.00..791,011.03 rows=10 width=133) (actual time=2,673.288..2,673.296 rows=10 loops=1)

  • (cost=0.00..19.70 rows=970 width=32) (actual time=0.004..0.004 rows=0 loops=1) --More--0.56..8.32 rows=1 width=46) (actual time=0.025..0.025 rows=1 loops=46322) (cost=--More--ext) Index Cond: ((id)::text = (d.first_place_id)::t--More--8.32 rows=1 width=46) (actual time=0.023..0.023 rows=1 loops=46322) (cost=0.56..--More--
  • (actual time=55.957..69.273 rows=46322 loops=1)31..6798.11 rows=46322 width=36)--More--
2. 29.336 2,673.287 ↑ 4,632.2 10 1

Sort (cost=791,011.00..791,126.81 rows=46,322 width=133) (actual time=2,673.286..2,673.287 rows=10 loops=1)

  • Sort Key: d.score
  • Sort Method: top-N heapsort Memory: 29kB
3. 161.977 2,643.951 ↑ 1.0 46,322 1

Merge Join (cost=15,208.79..790,010.00 rows=46,322 width=133) (actual time=166.761..2,643.951 rows=46,322 loops=1)

  • Merge Cond: (d.id = d_1.id)
4. 1,093.863 2,481.974 ↑ 1.0 46,322 1

Nested Loop (cost=8,526.48..781,122.89 rows=46,322 width=133) (actual time=110.791..2,481.974 rows=46,322 loops=1)

  • Group Key: d_1.id
  • Filter: (max((count(v_1.duplicate_candidate_id))) <= 5)=28) (actual time=55.962..97.862 rows=46322 loops=1)31..7608.75 rows=46322 width--More--
5. 1,169.224 1,388.111 ↑ 1.0 46,322 1

Nested Loop (cost=8,525.92..395,584.13 rows=46,322 width=112) (actual time=110.707..1,388.111 rows=46,322 loops=1)

  • Index Cond: ((id)::text = (d.second_place_id)::text)tual time=55.966..132.079 rows=46322 loops=1).31..8305.58 rows=200 width=24) (ac--More--
  • Group Key: d_1.id, v_1.type
6. 218.887 218.887 ↑ 1.0 46,322 1

GroupAggregate (cost=8,525.36..9,582.15 rows=46,322 width=123) (actual time=110.655..218.887 rows=46,322 loops=1)

  • Group Key: d.idab0aa}'::uuid[] <@ array_agg(v.user_id)))(NOT ('{84323281-b363-45e4-8b15-685ef79--More--s=46322 width=107) (actual time=110.454..144.724 rows=46322 loops=1).8771.52 row--More--_id) Merge Cond: (d.id = v.duplicate_candidate--More--22 width=91) (actual time=110.438..130.365 rows=46322 loops=1)..8573.34 rows=463--More--
  • Sort Key: d.id5176kB Sort Method: external merge Disk: --More--761 rows=46322 loops=1).00..2491.22 rows=46322 width=91) (actual time=0.977..14.--More--
  • Filter: (NOT fulfilled)th=32) (actual time=0.013..0.013 rows=0 loops=1) (cost=67.82..70.25 rows=970 wid--More--
  • Sort Key: v.duplicate_candidate_idB Sort Method: quicksort Memory: 25k--More--
  • Sort Key: d_1.id, v_1.type
  • Sort Method: quicksort Memory: 3537kBs=46322 width=36) (actual time=22.559..32.277 rows=46322 loops=1)25..3092.49 row--More--d_1.id) Hash Cond: (v_1.duplicate_candidate_id = --More--st=0.00..19.70 rows=970 width=20) (actual time=0.004..0.004 rows=0 loops=1) (co--More--22 width=16) (actual time=22.252..22.253 rows=46322 loops=1)22..2491.22 rows=463--More--Usage: 2684kB Buckets: 65536 Batches: 1 Memory --More--.595 rows=46322 loops=1)0.00..2491.22 rows=46322 width=16) (actual time=0.956..9--More--
Planning time : 5.572 ms
Execution time : 2,675.975 ms