explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GYL

Settings
# exclusive inclusive rows x rows loops node
1. 517.192 600.252 ↓ 3.3 10 1

CTE Scan on bc_query q (cost=10,718.18..11,394.80 rows=3 width=1,646) (actual time=598.141..600.252 rows=10 loops=1)

2.          

CTE bc_query

3. 0.034 517.096 ↓ 3.3 10 1

Limit (cost=1,179.92..1,179.92 rows=3 width=1,255) (actual time=517.049..517.096 rows=10 loops=1)

4. 1.267 517.062 ↓ 3.3 10 1

Sort (cost=1,179.92..1,179.92 rows=3 width=1,255) (actual time=517.047..517.062 rows=10 loops=1)

  • Sort Key: users.first_name, users.last_name, users.id
  • Sort Method: top-N heapsort Memory: 35kB
5. 7.956 515.795 ↓ 376.7 1,130 1

WindowAgg (cost=334.62..1,179.89 rows=3 width=1,255) (actual time=514.005..515.795 rows=1,130 loops=1)

6. 3.187 507.839 ↓ 376.7 1,130 1

Nested Loop Left Join (cost=334.62..1,179.85 rows=3 width=1,251) (actual time=0.686..507.839 rows=1,130 loops=1)

  • Filter: (COALESCE(((sum(CASE WHEN (t.type = 'credit'::text) THEN t.value WHEN (t.type = 'debit'::text) THEN (- t.value) ELSE '0'::bigint END))::bigint), '0'::bigint) > '1'::bigint)
  • Rows Removed by Filter: 30
7. 1.445 59.212 ↓ 1,160.0 1,160 1

Nested Loop Left Join (cost=70.64..915.33 rows=1 width=1,235) (actual time=0.546..59.212 rows=1,160 loops=1)

8. 1.660 51.967 ↓ 1,160.0 1,160 1

Nested Loop Left Join (cost=70.36..914.75 rows=1 width=1,166) (actual time=0.536..51.967 rows=1,160 loops=1)

9. 2.697 32.907 ↓ 1,160.0 1,160 1

Nested Loop Left Join (cost=70.08..906.40 rows=1 width=1,126) (actual time=0.482..32.907 rows=1,160 loops=1)

  • Filter: ((applicants.id IS NULL) OR applicants.approved)
  • Rows Removed by Filter: 67
10. 2.231 14.259 ↓ 1,227.0 1,227 1

Nested Loop Left Join (cost=61.76..898.06 rows=1 width=1,110) (actual time=0.443..14.259 rows=1,227 loops=1)

11. 1.968 4.666 ↓ 1,227.0 1,227 1

Hash Left Join (cost=61.48..895.31 rows=1 width=157) (actual time=0.430..4.666 rows=1,227 loops=1)

  • Hash Cond: (users.id = user_roles.user_id)
  • Filter: (user_roles.role IS NULL)
  • Rows Removed by Filter: 9
12. 2.321 2.495 ↓ 1.1 1,235 1

Bitmap Heap Scan on users (cost=42.86..870.81 rows=1,114 width=157) (actual time=0.210..2.495 rows=1,235 loops=1)

  • Recheck Cond: ((tenant_id)::text = 'skirtsports'::text)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 138
  • Heap Blocks: exact=233
13. 0.174 0.174 ↑ 1.0 1,373 1

Bitmap Index Scan on users_tenant_id_slug_unique (cost=0.00..42.59 rows=1,373 width=0) (actual time=0.173..0.174 rows=1,373 loops=1)

  • Index Cond: ((tenant_id)::text = 'skirtsports'::text)
14. 0.108 0.203 ↑ 1.0 605 1

Hash (cost=11.05..11.05 rows=605 width=10) (actual time=0.203..0.203 rows=605 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
15. 0.095 0.095 ↑ 1.0 605 1

Seq Scan on user_roles (cost=0.00..11.05 rows=605 width=10) (actual time=0.009..0.095 rows=605 loops=1)

16. 7.362 7.362 ↑ 1.0 1 1,227

Index Scan using profiles_user_id_unique_idx on profiles (cost=0.28..2.74 rows=1 width=957) (actual time=0.005..0.006 rows=1 loops=1,227)

  • Index Cond: (user_id = users.id)
17. 2.454 15.951 ↑ 1.0 1 1,227

Limit (cost=8.32..8.33 rows=1 width=21) (actual time=0.013..0.013 rows=1 loops=1,227)

18. 4.908 13.497 ↑ 1.0 1 1,227

Sort (cost=8.32..8.33 rows=1 width=21) (actual time=0.011..0.011 rows=1 loops=1,227)

  • Sort Key: applicants.id DESC
  • Sort Method: quicksort Memory: 25kB
19. 8.589 8.589 ↑ 1.0 1 1,227

Index Scan using applicants_user_id on applicants (cost=0.29..8.31 rows=1 width=21) (actual time=0.006..0.007 rows=1 loops=1,227)

  • Index Cond: (user_id = users.id)
20. 10.440 17.400 ↑ 1.0 1 1,160

GroupAggregate (cost=0.28..8.32 rows=1 width=44) (actual time=0.014..0.015 rows=1 loops=1,160)

  • Group Key: discount_codes.user_id
21. 6.960 6.960 ↑ 1.0 1 1,160

Index Scan using discount_codes_user_id_visible_idx on discount_codes (cost=0.28..8.30 rows=1 width=13) (actual time=0.005..0.006 rows=1 loops=1,160)

  • Index Cond: ((user_id = users.id) AND (visible = true))
  • Filter: visible
22. 5.800 5.800 ↓ 0.0 0 1,160

Index Scan using addresses_user_id_address_type_unique on addresses (cost=0.28..0.57 rows=1 width=73) (actual time=0.004..0.005 rows=0 loops=1,160)

  • Index Cond: ((user_id = users.id) AND (address_type = 'shipping'::text))
23. 19.720 445.440 ↑ 8.0 1 1,160

GroupAggregate (cost=263.98..264.34 rows=8 width=56) (actual time=0.384..0.384 rows=1 loops=1,160)

  • Group Key: t.user_id, t.value_type, t.currency
24. 17.400 425.720 ↓ 3.2 26 1,160

Sort (cost=263.98..264.00 rows=8 width=54) (actual time=0.365..0.367 rows=26 loops=1,160)

  • Sort Key: t.currency
  • Sort Method: quicksort Memory: 25kB
25. 12.660 408.320 ↓ 3.2 26 1,160

Nested Loop Left Join (cost=0.70..263.86 rows=8 width=54) (actual time=0.021..0.352 rows=26 loops=1,160)

  • Filter: (t.created_at > COALESCE(user_statements.closing_ts, '-infinity'::timestamp without time zone))
26. 61.480 61.480 ↓ 1.0 26 1,160

Index Scan using transactions_user_id on transactions t (cost=0.29..51.67 rows=25 width=62) (actual time=0.007..0.053 rows=26 loops=1,160)

  • Index Cond: (user_id = users.id)
  • Filter: (value_type = 'points'::transaction_value_type)
  • Rows Removed by Filter: 0
27. 30.380 334.180 ↓ 0.0 0 30,380

Limit (cost=0.41..8.46 rows=1 width=282) (actual time=0.011..0.011 rows=0 loops=30,380)

28. 303.800 303.800 ↓ 0.0 0 30,380

Index Scan Backward using uq_stmt_per_user_per_date on user_statements (cost=0.41..8.46 rows=1 width=282) (actual time=0.010..0.010 rows=0 loops=30,380)

  • Index Cond: ((user_id = t.user_id) AND (value_type = 'points'::transaction_value_type))
  • Filter: ((tenant_id)::text = 'demo'::text)
  • Rows Removed by Filter: 11
29.          

CTE txn

30. 0.161 80.396 ↑ 8.4 1,188 1

Subquery Scan on calc_user_balances (cost=9,246.36..9,538.25 rows=9,982 width=56) (actual time=78.814..80.396 rows=1,188 loops=1)

31. 1.034 80.235 ↑ 8.4 1,188 1

Merge Full Join (cost=9,246.36..9,438.43 rows=9,982 width=128) (actual time=78.812..80.235 rows=1,188 loops=1)

  • Merge Cond: ((tb.user_id = stmt_1.user_id) AND (tb.value_type = stmt_1.value_type) AND ((COALESCE(tb.currency, ''::text)) = (COALESCE(stmt_1.currency, ''::text))))
32.          

CTE most_recent_user_stmts

33. 1.341 15.956 ↑ 3.6 1,170 1

Unique (cost=1,180.28..1,243.08 rows=4,229 width=74) (actual time=12.979..15.956 rows=1,170 loops=1)

34. 8.736 14.615 ↑ 1.0 6,280 1

Sort (cost=1,180.28..1,195.98 rows=6,280 width=74) (actual time=12.975..14.615 rows=6,280 loops=1)

  • Sort Key: user_statements_1.user_id, user_statements_1.value_type, user_statements_1.currency, user_statements_1.closing_ts DESC
  • Sort Method: quicksort Memory: 683kB
35. 5.879 5.879 ↑ 1.0 6,280 1

Seq Scan on user_statements user_statements_1 (cost=0.00..784.12 rows=6,280 width=74) (actual time=0.674..5.879 rows=6,280 loops=1)

  • Filter: ((tenant_id)::text = 'skirtsports'::text)
  • Rows Removed by Filter: 15730
36.          

CTE txn_balances

37. 2.103 77.755 ↑ 26.1 382 1

HashAggregate (cost=6,601.64..6,801.28 rows=9,982 width=107) (actual time=77.449..77.755 rows=382 loops=1)

  • Group Key: t_1.tenant_id, t_1.user_id, t_1.value_type, t_1.currency
38. 7.463 75.652 ↑ 12.0 878 1

Merge Right Join (cost=5,737.70..6,311.93 rows=10,535 width=65) (actual time=58.676..75.652 rows=878 loops=1)

  • Merge Cond: (stmt.user_id = t_1.user_id)
  • Filter: (t_1.created_at > COALESCE(stmt.closing_ts, '-infinity'::timestamp without time zone))
  • Rows Removed by Filter: 30381
39. 0.569 16.938 ↑ 3.6 1,170 1

Sort (cost=339.29..349.87 rows=4,229 width=12) (actual time=16.695..16.938 rows=1,170 loops=1)

  • Sort Key: stmt.user_id
  • Sort Method: quicksort Memory: 103kB
40. 16.369 16.369 ↑ 3.6 1,170 1

CTE Scan on most_recent_user_stmts stmt (cost=0.00..84.58 rows=4,229 width=12) (actual time=12.988..16.369 rows=1,170 loops=1)

41. 23.644 51.251 ↓ 1.0 31,259 1

Sort (cost=5,398.40..5,476.26 rows=31,142 width=73) (actual time=41.867..51.251 rows=31,259 loops=1)

  • Sort Key: t_1.user_id
  • Sort Method: quicksort Memory: 3211kB
42. 27.607 27.607 ↓ 1.0 31,259 1

Seq Scan on transactions t_1 (cost=0.00..3,074.19 rows=31,142 width=73) (actual time=0.172..27.607 rows=31,259 loops=1)

  • Filter: ((tenant_id)::text = 'skirtsports'::text)
  • Rows Removed by Filter: 71328
43. 0.329 78.315 ↑ 26.1 382 1

Sort (cost=862.70..887.65 rows=9,982 width=56) (actual time=78.179..78.315 rows=382 loops=1)

  • Sort Key: tb.user_id, tb.value_type, (COALESCE(tb.currency, ''::text))
  • Sort Method: quicksort Memory: 54kB
44. 77.986 77.986 ↑ 26.1 382 1

CTE Scan on txn_balances tb (cost=0.00..199.64 rows=9,982 width=56) (actual time=77.453..77.986 rows=382 loops=1)

45. 0.546 0.886 ↑ 3.6 1,170 1

Sort (cost=339.29..349.87 rows=4,229 width=56) (actual time=0.625..0.886 rows=1,170 loops=1)

  • Sort Key: stmt_1.user_id, stmt_1.value_type, (COALESCE(stmt_1.currency, ''::text))
  • Sort Method: quicksort Memory: 140kB
46. 0.340 0.340 ↑ 3.6 1,170 1

CTE Scan on most_recent_user_stmts stmt_1 (cost=0.00..84.58 rows=4,229 width=56) (actual time=0.002..0.340 rows=1,170 loops=1)

47.          

SubPlan (forCTE Scan)

48. 0.320 83.060 ↑ 44.0 1 10

GroupAggregate (cost=0.00..225.52 rows=44 width=36) (actual time=8.306..8.306 rows=1 loops=10)

  • Group Key: txn.user_id
49. 82.740 82.740 ↑ 50.0 1 10

CTE Scan on txn (cost=0.00..224.59 rows=50 width=56) (actual time=8.143..8.274 rows=1 loops=10)

  • Filter: (user_id = q.user_id)
  • Rows Removed by Filter: 1187
Planning time : 2.622 ms