explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HRgP : Optimization for: plan #bdh

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.016 202.690 ↑ 1.0 10 1

Nested Loop (cost=17,730.10..19,526.50 rows=10 width=133) (actual time=166.349..202.690 rows=10 loops=1)

2. 0.026 202.504 ↑ 1.0 10 1

Nested Loop (cost=17,729.54..19,440.70 rows=10 width=112) (actual time=166.323..202.504 rows=10 loops=1)

3. 2.858 202.258 ↑ 1.0 10 1

Merge Join (cost=17,728.98..19,354.90 rows=10 width=91) (actual time=166.286..202.258 rows=10 loops=1)

  • Merge Cond: (c.id = d_1.id)
4. 0.014 114.160 ↑ 1.0 10 1

Sort (cost=11,046.67..11,046.69 rows=10 width=91) (actual time=114.157..114.160 rows=10 loops=1)

  • Sort Key: c.id
  • Sort Method: quicksort Memory: 26kB
5. 0.005 114.146 ↑ 1.0 10 1

Subquery Scan on c (cost=11,046.38..11,046.50 rows=10 width=91) (actual time=114.139..114.146 rows=10 loops=1)

6. 0.004 114.141 ↑ 1.0 10 1

Limit (cost=11,046.38..11,046.40 rows=10 width=123) (actual time=114.138..114.141 rows=10 loops=1)

7. 10.724 114.137 ↑ 4,632.2 10 1

Sort (cost=11,046.38..11,162.18 rows=46,322 width=123) (actual time=114.137..114.137 rows=10 loops=1)

  • Sort Key: d.score
  • Sort Method: top-N heapsort Memory: 27kB
8. 33.190 103.413 ↑ 1.0 46,322 1

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

  • Group Key: d.id
  • Filter: (NOT ('{84323281-b363-45e4-8b15-685ef79ab0aa}'::uuid[] <@ array_agg(v.user_id)))
9. 7.666 70.223 ↑ 1.0 46,322 1

Merge Left Join (cost=8,525.36..8,771.52 rows=46,322 width=107) (actual time=55.129..70.223 rows=46,322 loops=1)

  • Merge Cond: (d.id = v.duplicate_candidate_id)
10. 51.050 62.546 ↑ 1.0 46,322 1

Sort (cost=8,457.54..8,573.34 rows=46,322 width=91) (actual time=55.114..62.546 rows=46,322 loops=1)

  • Sort Key: d.id
  • Sort Method: external merge Disk: 5,176kB
11. 11.496 11.496 ↑ 1.0 46,322 1

Seq Scan on playground_duplicate_candidates d (cost=0.00..2,491.22 rows=46,322 width=91) (actual time=0.862..11.496 rows=46,322 loops=1)

  • Filter: (NOT fulfilled)
12. 0.008 0.011 ↓ 0.0 0 1

Sort (cost=67.82..70.25 rows=970 width=32) (actual time=0.011..0.011 rows=0 loops=1)

  • Sort Key: v.duplicate_candidate_id
  • Sort Method: quicksort Memory: 25kB
13. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on playground_votes v (cost=0.00..19.70 rows=970 width=32) (actual time=0.003..0.003 rows=0 loops=1)

14. 14.216 85.240 ↓ 212.9 42,582 1

GroupAggregate (cost=6,682.31..8,305.58 rows=200 width=24) (actual time=50.072..85.240 rows=42,582 loops=1)

  • Group Key: d_1.id
  • Filter: (max((count(v_1.duplicate_candidate_id))) <= 2)
15. 14.527 71.024 ↑ 1.1 42,583 1

GroupAggregate (cost=6,682.31..7,608.75 rows=46,322 width=28) (actual time=50.067..71.024 rows=42,583 loops=1)

  • Group Key: d_1.id, v_1.type
16. 28.372 56.497 ↑ 1.1 42,584 1

Sort (cost=6,682.31..6,798.11 rows=46,322 width=36) (actual time=50.061..56.497 rows=42,584 loops=1)

  • Sort Key: d_1.id, v_1.type
  • Sort Method: quicksort Memory: 3,537kB
17. 9.273 28.125 ↑ 1.0 46,322 1

Hash Right Join (cost=3,070.25..3,092.49 rows=46,322 width=36) (actual time=19.084..28.125 rows=46,322 loops=1)

  • Hash Cond: (v_1.duplicate_candidate_id = d_1.id)
18. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on playground_votes v_1 (cost=0.00..19.70 rows=970 width=20) (actual time=0.009..0.009 rows=0 loops=1)

19. 10.301 18.843 ↑ 1.0 46,322 1

Hash (cost=2,491.22..2,491.22 rows=46,322 width=16) (actual time=18.843..18.843 rows=46,322 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,684kB
20. 8.542 8.542 ↑ 1.0 46,322 1

Seq Scan on playground_duplicate_candidates d_1 (cost=0.00..2,491.22 rows=46,322 width=16) (actual time=0.755..8.542 rows=46,322 loops=1)

21. 0.220 0.220 ↑ 1.0 1 10

Index Scan using places_pkey on places p1 (cost=0.56..8.58 rows=1 width=46) (actual time=0.022..0.022 rows=1 loops=10)

  • Index Cond: ((id)::text = (c.first_place_id)::text)
22. 0.170 0.170 ↑ 1.0 1 10

Index Scan using places_pkey on places p2 (cost=0.56..8.58 rows=1 width=46) (actual time=0.017..0.017 rows=1 loops=10)

  • Index Cond: ((id)::text = (c.second_place_id)::text)