explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hpDM

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 29.880 ↑ 1.0 50 1

Limit (cost=78,703.29..78,703.41 rows=50 width=40) (actual time=29.872..29.880 rows=50 loops=1)

2. 0.587 29.874 ↑ 26.1 50 1

Sort (cost=78,703.29..78,706.55 rows=1,306 width=40) (actual time=29.870..29.874 rows=50 loops=1)

  • Sort Key: ((((count(*)))::numeric / ((count(*)))::numeric)) DESC
  • Sort Method: top-N heapsort Memory: 30kB
3. 1.449 29.287 ↓ 1.2 1,515 1

Nested Loop (cost=78,493.37..78,659.90 rows=1,306 width=40) (actual time=27.158..29.287 rows=1,515 loops=1)

  • Join Filter: ((((count(*)))::numeric / ((count(*)))::numeric) < '1'::numeric)
  • Rows Removed by Join Filter: 1
4. 0.028 0.172 ↑ 1.0 1 1

Aggregate (cost=5,572.07..5,572.08 rows=1 width=8) (actual time=0.171..0.172 rows=1 loops=1)

5. 0.144 0.144 ↑ 26.0 280 1

Index Only Scan using usr_id_idx on faves (cost=0.56..5,553.84 rows=7,293 width=0) (actual time=0.034..0.144 rows=280 loops=1)

  • Index Cond: (user_id = 1,946,645)
  • Heap Fetches: 280
6. 0.556 27.666 ↑ 2.6 1,516 1

Unique (cost=72,921.29..72,960.47 rows=3,918 width=48) (actual time=26.979..27.666 rows=1,516 loops=1)

7. 0.503 27.110 ↑ 2.6 1,516 1

Sort (cost=72,921.29..72,931.09 rows=3,918 width=48) (actual time=26.978..27.110 rows=1,516 loops=1)

  • Sort Key: faves_1.user_id, (count(*)), (array_agg(faves_1.submission_id))
  • Sort Method: quicksort Memory: 227kB
8. 1.876 26.607 ↑ 2.6 1,516 1

GroupAggregate (cost=71,605.76..72,687.47 rows=3,918 width=48) (actual time=24.191..26.607 rows=1,516 loops=1)

  • Group Key: faves_1.user_id
9. 1.905 24.731 ↑ 22.1 4,676 1

Sort (cost=71,605.76..71,863.95 rows=103,273 width=16) (actual time=24.175..24.731 rows=4,676 loops=1)

  • Sort Key: faves_1.user_id
  • Sort Method: quicksort Memory: 412kB
10. 11.211 22.826 ↑ 22.1 4,676 1

Gather (cost=1,080.75..63,005.14 rows=103,273 width=16) (actual time=0.524..22.826 rows=4,676 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 0.375 11.615 ↑ 27.6 1,559 3 / 3

Nested Loop (cost=80.75..51,677.84 rows=43,030 width=16) (actual time=0.160..11.615 rows=1,559 loops=3)

12. 0.059 0.133 ↑ 32.7 93 3 / 3

Parallel Bitmap Heap Scan on faves faves_2 (cost=80.19..7,871.67 rows=3,039 width=8) (actual time=0.112..0.133 rows=93 loops=3)

  • Recheck Cond: (user_id = 1,946,645)
  • Heap Blocks: exact=2
13. 0.074 0.074 ↑ 26.0 280 1 / 3

Bitmap Index Scan on usr_id_idx (cost=0.00..78.36 rows=7,293 width=0) (actual time=0.223..0.223 rows=280 loops=1)

  • Index Cond: (user_id = 1,946,645)
14. 11.107 11.107 ↓ 1.2 17 280 / 3

Index Scan using subm_id_idx on faves faves_1 (cost=0.56..14.27 rows=14 width=16) (actual time=0.023..0.119 rows=17 loops=280)

  • Index Cond: (submission_id = faves_2.submission_id)
Planning time : 0.448 ms
Execution time : 30.249 ms