explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ogMK

Settings
# exclusive inclusive rows x rows loops node
1. 0.042 31.449 ↑ 2.5 41 1

Subquery Scan on t (cost=12,481.25..26,624.04 rows=101 width=633) (actual time=24.443..31.449 rows=41 loops=1)

2. 0.037 24.273 ↑ 2.5 41 1

Unique (cost=12,481.25..12,483.27 rows=101 width=617) (actual time=24.230..24.273 rows=41 loops=1)

3. 0.074 24.236 ↑ 2.5 41 1

Sort (cost=12,481.25..12,481.50 rows=101 width=617) (actual time=24.229..24.236 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.598 24.162 ↑ 2.5 41 1

Gather (cost=1,340.70..12,477.88 rows=101 width=617) (actual time=8.343..24.162 rows=41 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 0.847 21.564 ↑ 3.0 20 2

Hash Join (cost=340.70..11,467.78 rows=59 width=617) (actual time=6.901..21.564 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 17.867 ↑ 605.0 20 2

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

7. 0.270 17.854 ↑ 6.0 20 2

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

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

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

9. 11.778 11.778 ↑ 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.026..11.778 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.603 2.850 ↑ 1.0 1,985 2

Hash (cost=310.88..310.88 rows=1,988 width=1,053) (actual time=2.849..2.850 rows=1,985 loops=2)

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

Seq Scan on bulk_match_users_info a (cost=0.00..310.88 rows=1,988 width=1,053) (actual time=0.019..1.247 rows=1,985 loops=2)

12.          

SubPlan (forSubquery Scan)

13. 0.041 7.052 ↑ 1.0 1 41

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

  • Group Key: a_1.match_id
14. 7.011 7.011 ↓ 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.136..0.171 rows=3 loops=41)

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

GroupAggregate (cost=0.42..8.46 rows=1 width=12) (actual time=0.002..0.002 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.521 ms
Execution time : 31.936 ms