explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1yQU

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 30.592 ↑ 2.5 41 1

Subquery Scan on t (cost=12,481.17..26,623.96 rows=101 width=633) (actual time=23.476..30.592 rows=41 loops=1)

2. 0.060 23.308 ↑ 2.5 41 1

Unique (cost=12,481.17..12,483.19 rows=101 width=617) (actual time=23.241..23.308 rows=41 loops=1)

3. 0.071 23.248 ↑ 2.5 41 1

Sort (cost=12,481.17..12,481.42 rows=101 width=617) (actual time=23.240..23.248 rows=41 loops=1)

  • Sort Key: t_1.score DESC, (CASE WHEN (a.mentorship_limit = '-1'::integer) THEN NULL::integer ELSE a.mentorship_limit END) DESC, (unnest(t_1.mentor_ids)), a.mentorship_limit, ((a.rejected)::jsonb), t_1.match_equation, ((a.match_data)::jsonb)
  • Sort Method: quicksort Memory: 204kB
4. 2.940 23.177 ↑ 2.5 41 1

Gather (cost=1,340.62..12,477.81 rows=101 width=617) (actual time=8.557..23.177 rows=41 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 0.766 20.237 ↑ 3.0 20 2

Hash Join (cost=340.62..11,467.71 rows=59 width=617) (actual time=6.862..20.237 rows=20 loops=2)

  • Hash Cond: ((t_1.job_id = a.job_id) AND ((unnest(t_1.mentor_ids)) = a.user_id))
6. 0.013 16.552 ↑ 605.0 20 2

ProjectSet (cost=0.00..11,118.68 rows=12,100 width=553) (actual time=3.822..16.552 rows=20 loops=2)

7. 0.273 16.539 ↑ 6.0 20 2

Subquery Scan on t_1 (cost=0.00..11,057.27 rows=121 width=573) (actual time=3.820..16.539 rows=20 loops=2)

  • Filter: (t_1.k = 48791)
  • Rows Removed by Filter: 3504
8. 5.852 16.266 ↑ 403.7 3,525 2

ProjectSet (cost=0.00..10,754.87 rows=1,423,100 width=633) (actual time=0.038..16.266 rows=3,525 loops=2)

9. 10.414 10.414 ↑ 5.9 2,400 2

Parallel Seq Scan on bulk_match_precomputed (cost=0.00..2,856.67 rows=14,231 width=575) (actual time=0.029..10.414 rows=2,400 loops=2)

  • Filter: ((job_id = 4004) AND ((((COALESCE((((match_equation ->> 'favorites'::text))::numeric * '50'::numeric), '0'::numeric) + COALESCE((((match_equation ->> 'major'::text))::numeric * '50'::numeric), '0'::numeric)) + COALESCE((((match_equation ->> 'location'::text))::numeric * '10'::numeric), '0'::numeric)) + COALESCE((((match_equation ->> 'industry-interests'::text))::numeric * '100'::numeric), '0'::numeric)) <> '0'::numeric))
  • Rows Removed by Filter: 9972
10. 1.686 2.919 ↑ 1.0 1,985 2

Hash (cost=310.85..310.85 rows=1,985 width=1,053) (actual time=2.919..2.919 rows=1,985 loops=2)

  • Buckets: 2048 Batches: 1 Memory Usage: 2105kB
11. 1.233 1.233 ↑ 1.0 1,985 2

Seq Scan on bulk_match_users_info a (cost=0.00..310.85 rows=1,985 width=1,053) (actual time=0.021..1.233 rows=1,985 loops=2)

12.          

SubPlan (forSubquery Scan)

13. 0.041 7.134 ↑ 1.0 1 41

GroupAggregate (cost=0.42..131.54 rows=1 width=12) (actual time=0.174..0.174 rows=1 loops=41)

  • Group Key: a_1.match_id
14. 7.093 7.093 ↓ 3.0 3 41

Index Scan using filterbyjob on bulk_matches a_1 (cost=0.42..131.52 rows=1 width=4) (actual time=0.137..0.173 rows=3 loops=41)

  • Index Cond: (job_id = 4004)
  • Filter: (match_id = t.mentor_id)
  • Rows Removed by Filter: 1238
15. 0.041 0.123 ↓ 0.0 0 41

GroupAggregate (cost=0.42..8.46 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=41)

  • Group Key: a_2.match_id
16. 0.082 0.082 ↓ 0.0 0 41

Index Scan using filterbyjob on bulk_matches a_2 (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=41)

  • Index Cond: ((job_id = 4004) AND (status = 'confirmed'::text))
  • Filter: (match_id = t.mentor_id)
Planning time : 0.590 ms
Execution time : 31.079 ms