explain.depesz.com

PostgreSQL's explain analyze made readable

Result: URSH

Settings
# exclusive inclusive rows x rows loops node
1. 3.649 79.694 ↑ 1.0 2 1

Limit (cost=276,162.92..276,162.92 rows=2 width=1,162) (actual time=76.046..79.694 rows=2 loops=1)

2. 0.000 76.045 ↑ 395.0 2 1

Sort (cost=276,162.92..276,164.89 rows=790 width=1,162) (actual time=76.045..76.045 rows=2 loops=1)

  • Sort Key: ((SubPlan 1)), (((SubPlan 2) + (SubPlan 3))) DESC, (random())
  • Sort Method: top-N heapsort Memory: 33kB
3. 0.000 79.285 ↑ 3.4 235 1

Gather (cost=27,024.41..276,155.02 rows=790 width=1,162) (actual time=34.084..79.285 rows=235 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.070 58.282 ↑ 4.2 78 3 / 3

Nested Loop Semi Join (cost=26,024.41..35,343.92 rows=329 width=1,114) (actual time=29.188..58.282 rows=78 loops=3)

  • Join Filter: (lego_moc.designer_id = u0.id)
5. 0.382 57.899 ↑ 4.2 78 3 / 3

Parallel Hash Semi Join (cost=26,023.43..34,949.52 rows=329 width=1,114) (actual time=29.173..57.899 rows=78 loops=3)

  • Hash Cond: (lego_moc.baseset_ptr_id = (((jsonb_array_elements((v0.value -> 'featured_moc_ids'::text))))::integer))
6. 2.716 38.272 ↓ 4.5 2,976 3 / 3

Nested Loop Left Join (cost=1,317.53..10,205.75 rows=658 width=1,114) (actual time=9.005..38.272 rows=2,976 loops=3)

7. 0.974 32.580 ↓ 4.5 2,976 3 / 3

Hash Left Join (cost=1,317.24..9,978.53 rows=658 width=1,090) (actual time=8.995..32.580 rows=2,976 loops=3)

  • Hash Cond: (accounts_userplan.plan_id = plans_plan.id)
8. 0.515 31.600 ↓ 4.5 2,976 3 / 3

Nested Loop Left Join (cost=1,316.13..9,974.22 rows=658 width=950) (actual time=8.981..31.600 rows=2,976 loops=3)

9. 0.988 25.134 ↓ 4.5 2,976 3 / 3

Nested Loop (cost=1,315.71..9,658.24 rows=658 width=873) (actual time=8.975..25.134 rows=2,976 loops=3)

  • Join Filter: (lego_moc.designer_id = auth_user.id)
10. 0.922 18.195 ↓ 4.5 2,976 3 / 3

Hash Join (cost=1,315.29..6,902.02 rows=658 width=741) (actual time=8.967..18.195 rows=2,976 loops=3)

  • Hash Cond: (lego_moc.designer_id = lego_designer.user_ptr_id)
11. 3.263 16.204 ↓ 4.5 2,976 3 / 3

Hash Join (cost=1,139.54..6,724.55 rows=658 width=736) (actual time=7.869..16.204 rows=2,976 loops=3)

  • Hash Cond: (lego_baseset.id = lego_moc.baseset_ptr_id)
12. 5.087 5.087 ↑ 1.3 20,113 3 / 3

Parallel Seq Scan on lego_baseset (cost=0.00..5,518.96 rows=25,161 width=604) (actual time=0.004..5.087 rows=20,113 loops=3)

  • Filter: is_active
  • Rows Removed by Filter: 684
13. 2.436 7.854 ↓ 6.0 9,771 3 / 3

Hash (cost=1,119.14..1,119.14 rows=1,632 width=132) (actual time=7.854..7.854 rows=9,771 loops=3)

  • Buckets: 16,384 (originally 2048) Batches: 1 (originally 1) Memory Usage: 1,229kB
14. 5.411 5.418 ↓ 6.0 9,771 3 / 3

Seq Scan on lego_moc (cost=14.29..1,119.14 rows=1,632 width=132) (actual time=0.025..5.418 rows=9,771 loops=3)

  • Filter: (is_for_sale AND is_hosted AND (designer_id <> 347711) AND (NOT (hashed SubPlan 4)))
  • Rows Removed by Filter: 22,086
15.          

SubPlan (for Seq Scan)

16. 0.007 0.007 ↓ 0.0 0 3 / 3

Index Scan using accounts_mocpurchase_user_id_dded3d7b on accounts_mocpurchase u0_2 (cost=0.29..14.28 rows=3 width=4) (actual time=0.007..0.007 rows=0 loops=3)

  • Index Cond: (user_id = 347,711)
  • Filter: is_active
17. 0.622 1.069 ↑ 1.0 6,522 3 / 3

Hash (cost=94.22..94.22 rows=6,522 width=5) (actual time=1.069..1.069 rows=6,522 loops=3)

  • Buckets: 8,192 Batches: 1 Memory Usage: 300kB
18. 0.447 0.447 ↑ 1.0 6,522 3 / 3

Seq Scan on lego_designer (cost=0.00..94.22 rows=6,522 width=5) (actual time=0.027..0.447 rows=6,522 loops=3)

19. 5.951 5.951 ↑ 1.0 1 8,927 / 3

Index Scan using auth_user_pkey on auth_user (cost=0.42..4.18 rows=1 width=132) (actual time=0.002..0.002 rows=1 loops=8,927)

  • Index Cond: (id = lego_designer.user_ptr_id)
20. 5.951 5.951 ↑ 1.0 1 8,927 / 3

Index Scan using accounts_userplan_pkey on accounts_userplan (cost=0.42..0.48 rows=1 width=77) (actual time=0.002..0.002 rows=1 loops=8,927)

  • Index Cond: (auth_user.id = user_id)
21. 0.001 0.006 ↑ 1.0 5 3 / 3

Hash (cost=1.05..1.05 rows=5 width=140) (actual time=0.006..0.006 rows=5 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.005 0.005 ↑ 1.0 5 3 / 3

Seq Scan on plans_plan (cost=0.00..1.05 rows=5 width=140) (actual time=0.004..0.005 rows=5 loops=3)

23. 2.976 2.976 ↑ 1.0 1 8,927 / 3

Index Scan using lego_setstatstotal_pkey on lego_setstatstotal (cost=0.29..0.35 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=8,927)

  • Index Cond: (lego_baseset.id = set_id)
24. 0.052 19.245 ↑ 10.1 79 3 / 3

Parallel Hash (cost=24,695.90..24,695.90 rows=800 width=4) (actual time=19.245..19.245 rows=79 loops=3)

  • Buckets: 2,048 Batches: 1 Memory Usage: 112kB
25. 0.028 19.193 ↑ 6.3 79 3 / 3

Result (cost=17,193.10..24,687.90 rows=500 width=4) (actual time=10.949..19.193 rows=79 loops=3)

26. 0.104 19.165 ↑ 6.3 79 3 / 3

ProjectSet (cost=17,193.10..24,679.15 rows=500 width=32) (actual time=10.946..19.165 rows=79 loops=3)

27. 0.483 19.061 ↓ 7.6 38 3 / 3

Nested Loop Semi Join (cost=17,193.10..24,676.60 rows=5 width=412) (actual time=10.912..19.061 rows=38 loops=3)

28. 7.693 12.533 ↓ 37.8 756 3 / 3

Parallel Bitmap Heap Scan on accounts_usersetting v0 (cost=17,192.12..24,494.54 rows=20 width=416) (actual time=10.748..12.533 rows=756 loops=3)

  • Recheck Cond: ((name)::text = 'moc'::text)
  • Filter: ((value -> 'featured_moc'::text) = ANY ('{"\"moc\"","\"recent\"","\"all\""}'::jsonb[]))
  • Rows Removed by Filter: 34
  • Heap Blocks: exact=888
29. 4.840 4.840 ↓ 1.1 2,370 1 / 3

Bitmap Index Scan on accounts_usersetting_user_id_fef01d6d_uniq (cost=0.00..17,192.11 rows=2,242 width=0) (actual time=14.520..14.520 rows=2,370 loops=1)

  • Index Cond: ((name)::text = 'moc'::text)
30. 0.008 6.045 ↓ 0.0 0 2,267 / 3

Nested Loop (cost=0.98..9.09 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=2,267)

31. 0.781 5.290 ↑ 1.0 1 2,267 / 3

Nested Loop (cost=0.84..8.92 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=2,267)

32. 2.267 2.267 ↑ 1.0 1 2,267 / 3

Index Scan using auth_user_pkey on auth_user u0_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,267)

  • Index Cond: (id = v0.user_id)
  • Filter: is_active
  • Rows Removed by Filter: 0
33. 2.242 2.242 ↑ 1.0 1 2,242 / 3

Index Scan using accounts_userplan_pkey on accounts_userplan u1_1 (cost=0.42..0.48 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=2,242)

  • Index Cond: (user_id = u0_1.id)
34. 0.747 0.747 ↓ 0.0 0 2,242 / 3

Index Scan using plans_plan_pkey on plans_plan u2_1 (cost=0.13..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=2,242)

  • Index Cond: (id = u1_1.plan_id)
  • Filter: ((type)::text = 'D'::text)
  • Rows Removed by Filter: 1
35. 0.000 0.313 ↑ 1.0 1 235 / 3

Nested Loop (cost=0.98..1.19 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=235)

36. 0.078 0.235 ↑ 1.0 1 235 / 3

Nested Loop (cost=0.84..1.01 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=235)

37. 0.078 0.078 ↑ 1.0 1 235 / 3

Index Scan using auth_user_pkey on auth_user u0 (cost=0.42..0.53 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=235)

  • Index Cond: (id = auth_user.id)
  • Filter: is_active
38. 0.078 0.078 ↑ 1.0 1 235 / 3

Index Scan using accounts_userplan_pkey on accounts_userplan u1 (cost=0.42..0.48 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=235)

  • Index Cond: (user_id = u0.id)
39. 0.078 0.078 ↑ 1.0 1 235 / 3

Index Scan using plans_plan_pkey on plans_plan u2 (cost=0.13..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=235)

  • Index Cond: (id = u1.plan_id)
  • Filter: ((type)::text = 'D'::text)
40.          

SubPlan (for Gather)

41. 0.235 18.565 ↑ 1.0 1 235

Aggregate (cost=163.18..163.19 rows=1 width=32) (actual time=0.079..0.079 rows=1 loops=235)

42. 5.875 18.330 ↓ 0.0 0 235

Nested Loop (cost=0.72..163.17 rows=1 width=8) (actual time=0.074..0.078 rows=0 loops=235)

43. 3.290 3.290 ↓ 4.9 39 235

Index Scan using lego_moc_9654e4a1 on lego_moc m (cost=0.29..31.53 rows=8 width=4) (actual time=0.002..0.014 rows=39 loops=235)

  • Index Cond: (designer_id = lego_moc.designer_id)
44. 9.165 9.165 ↓ 0.0 0 9,165

Index Scan using test on lego_mocspotstatshistory i (cost=0.43..16.42 rows=3 width=12) (actual time=0.001..0.001 rows=0 loops=9,165)

  • Index Cond: ((moc_id = m.baseset_ptr_id) AND (day = (timezone('UTC'::text, now()))::date))
45. 0.000 0.235 ↑ 1.0 1 235

Aggregate (cost=40.99..41.00 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=235)

46. 0.000 0.235 ↓ 0.0 0 235

Nested Loop (cost=0.58..40.98 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=235)

47. 0.235 0.235 ↓ 0.0 0 235

Index Scan using accounts_mocpurchase_user_id_dded3d7b on accounts_mocpurchase p (cost=0.29..14.28 rows=3 width=4) (actual time=0.001..0.001 rows=0 loops=235)

  • Index Cond: (user_id = 347,711)
48. 0.000 0.000 ↓ 0.0 0

Index Scan using lego_moc_pkey on lego_moc m_1 (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: (baseset_ptr_id = p.moc_id)
  • Filter: (designer_id = lego_moc.designer_id)
49. 0.000 11.985 ↑ 1.0 1 235

Aggregate (cost=99.26..99.27 rows=1 width=8) (actual time=0.051..0.051 rows=1 loops=235)

50. 0.470 11.985 ↓ 0.0 0 235

Nested Loop (cost=0.71..99.26 rows=1 width=0) (actual time=0.050..0.051 rows=0 loops=235)

51. 2.350 2.350 ↓ 4.9 39 235

Index Scan using lego_moc_9654e4a1 on lego_moc m_2 (cost=0.29..31.53 rows=8 width=4) (actual time=0.001..0.010 rows=39 loops=235)

  • Index Cond: (designer_id = lego_moc.designer_id)
52. 9.165 9.165 ↓ 0.0 0 9,165

Index Only Scan using lego_usersetlike_user_id_1bd1ce00_uniq on lego_usersetlike l (cost=0.43..8.45 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=9,165)

  • Index Cond: ((user_id = 347,711) AND (set_id = m_2.baseset_ptr_id))
  • Heap Fetches: 12
Planning time : 5.775 ms
Execution time : 79.993 ms