explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LqeR

Settings
# exclusive inclusive rows x rows loops node
1. 0.154 170.287 ↑ 1.0 2 1

Limit (cost=401,804.52..401,804.52 rows=2 width=1,162) (actual time=170.133..170.287 rows=2 loops=1)

2. 0.289 170.133 ↑ 395.0 2 1

Sort (cost=401,804.52..401,806.49 rows=790 width=1,162) (actual time=170.133..170.133 rows=2 loops=1)

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

Gather (cost=27,024.41..401,796.62 rows=790 width=1,162) (actual time=29.657..169.844 rows=235 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.009 57.244 ↑ 4.2 78 3 / 3

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

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

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

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

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

7. 1.097 34.115 ↓ 4.5 2,976 3 / 3

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

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

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

9. 1.503 26.086 ↓ 4.5 2,976 3 / 3

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

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

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

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

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

  • Hash Cond: (lego_baseset.id = lego_moc.baseset_ptr_id)
12. 5.055 5.055 ↑ 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.055 rows=20,113 loops=3)

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

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

  • Buckets: 16,384 (originally 2048) Batches: 1 (originally 1) Memory Usage: 1,229kB
14. 5.314 5.321 ↓ 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.026..5.321 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.633 1.091 ↑ 1.0 6,522 3 / 3

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 300kB
18. 0.458 0.458 ↑ 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.458 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.007 ↑ 1.0 5 3 / 3

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

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

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

23. 5.951 5.951 ↑ 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.002..0.002 rows=1 loops=8,927)

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

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

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

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

26. 0.108 16.247 ↑ 6.3 79 3 / 3

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

27. 0.117 16.139 ↓ 7.6 38 3 / 3

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

28. 5.706 9.221 ↓ 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=7.345..9.221 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=885
29. 3.515 3.515 ↓ 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=10.546..10.546 rows=2,370 loops=1)

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

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

31. 0.025 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. 3.023 3.023 ↑ 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.004..0.004 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.078 0.392 ↑ 1.0 1 235 / 3

Nested Loop (cost=0.98..1.19 rows=1 width=8) (actual time=0.005..0.005 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.000 126.430 ↑ 1.0 1 235

Aggregate (cost=322.22..322.23 rows=1 width=32) (actual time=0.538..0.538 rows=1 loops=235)

42. 3.525 126.430 ↓ 0.0 0 235

Nested Loop (cost=0.72..322.21 rows=1 width=8) (actual time=0.500..0.538 rows=0 loops=235)

43. 3.760 3.760 ↓ 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.016 rows=39 loops=235)

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

Index Scan using lego_mocspotstatshistory_moc_id_spot_day_8c2dc309_uniq on lego_mocspotstatshistory i (cost=0.43..36.30 rows=3 width=12) (actual time=0.013..0.013 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.051..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.727 ms
Execution time : 170.570 ms