explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GMNu

Settings
# exclusive inclusive rows x rows loops node
1. 155.378 237.098 ↓ 3.3 10 1

CTE Scan on bc_query q (cost=11,345.28..12,021.90 rows=3 width=1,646) (actual time=234.739..237.098 rows=10 loops=1)

2.          

CTE bc_query

3. 0.029 155.274 ↓ 3.3 10 1

Limit (cost=1,807.01..1,807.02 rows=3 width=1,255) (actual time=155.229..155.274 rows=10 loops=1)

4. 1.420 155.245 ↓ 3.3 10 1

Sort (cost=1,807.01..1,807.02 rows=3 width=1,255) (actual time=155.226..155.245 rows=10 loops=1)

  • Sort Key: users.first_name, users.last_name, users.id
  • Sort Method: top-N heapsort Memory: 35kB
5. 6.833 153.825 ↓ 382.3 1,147 1

WindowAgg (cost=961.66..1,806.99 rows=3 width=1,255) (actual time=151.935..153.825 rows=1,147 loops=1)

6. 2.390 146.992 ↓ 382.3 1,147 1

Nested Loop Left Join (cost=961.66..1,806.93 rows=3 width=1,267) (actual time=3.690..146.992 rows=1,147 loops=1)

  • Filter: ((COALESCE(user_statements.lifetime_credit, '0'::bigint) + COALESCE(((sum(CASE WHEN (t.type = 'credit'::text) THEN t.value ELSE '0'::bigint END))::bigint), '0'::bigint)) > '1'::bigint)
  • Rows Removed by Filter: 13
7. 2.190 56.442 ↓ 1,160.0 1,160 1

Nested Loop Left Join (cost=909.80..1,754.52 rows=1 width=1,259) (actual time=3.612..56.442 rows=1,160 loops=1)

  • Join Filter: (user_statements.user_id = users.id)
  • Rows Removed by Join Filter: 1160
8. 1.802 50.772 ↓ 1,160.0 1,160 1

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

9. 2.069 44.330 ↓ 1,160.0 1,160 1

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

10. 1.504 27.181 ↓ 1,160.0 1,160 1

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

  • Filter: ((applicants.id IS NULL) OR applicants.approved)
  • Rows Removed by Filter: 67
11. 2.279 12.180 ↓ 1,227.0 1,227 1

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

12. 1.365 3.766 ↓ 1,227.0 1,227 1

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

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

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

  • Recheck Cond: ((tenant_id)::text = 'skirtsports'::text)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 138
  • Heap Blocks: exact=233
14. 0.168 0.168 ↑ 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.168..0.168 rows=1,373 loops=1)

  • Index Cond: ((tenant_id)::text = 'skirtsports'::text)
15. 0.129 0.225 ↑ 1.0 605 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
16. 0.096 0.096 ↑ 1.0 605 1

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

17. 6.135 6.135 ↑ 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.005 rows=1 loops=1,227)

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

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

19. 3.681 11.043 ↑ 1.0 1 1,227

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

  • Sort Key: applicants.id DESC
  • Sort Method: quicksort Memory: 25kB
20. 7.362 7.362 ↑ 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.005..0.006 rows=1 loops=1,227)

  • Index Cond: (user_id = users.id)
21. 9.280 15.080 ↑ 1.0 1 1,160

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

  • Group Key: discount_codes.user_id
22. 5.800 5.800 ↑ 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.005 rows=1 loops=1,160)

  • Index Cond: ((user_id = users.id) AND (visible = true))
  • Filter: visible
23. 4.640 4.640 ↓ 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.004 rows=0 loops=1,160)

  • Index Cond: ((user_id = users.id) AND (address_type = 'shipping'::text))
24. 0.000 3.480 ↑ 1.0 1 1,160

Limit (cost=839.16..839.16 rows=1 width=282) (actual time=0.003..0.003 rows=1 loops=1,160)

25. 0.424 3.480 ↑ 1.0 1 1,160

Sort (cost=839.16..839.16 rows=1 width=282) (actual time=0.003..0.003 rows=1 loops=1,160)

  • Sort Key: user_statements.closing_ts DESC
  • Sort Method: top-N heapsort Memory: 25kB
26. 3.056 3.056 ↓ 188.0 188 1

Seq Scan on user_statements (cost=0.00..839.15 rows=1 width=282) (actual time=0.039..3.056 rows=188 loops=1)

  • Filter: (((tenant_id)::text = 'demo'::text) AND (value_type = 'points'::transaction_value_type))
  • Rows Removed by Filter: 21822
27. 19.720 88.160 ↑ 8.0 1 1,160

GroupAggregate (cost=51.85..52.21 rows=8 width=56) (actual time=0.076..0.076 rows=1 loops=1,160)

  • Group Key: t.user_id, t.value_type, t.currency
28. 12.760 68.440 ↓ 3.2 26 1,160

Sort (cost=51.85..51.87 rows=8 width=54) (actual time=0.056..0.059 rows=26 loops=1,160)

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

Index Scan using transactions_user_id on transactions t (cost=0.29..51.73 rows=8 width=54) (actual time=0.007..0.048 rows=26 loops=1,160)

  • Index Cond: (user_id = users.id)
  • Filter: ((created_at > COALESCE(user_statements.closing_ts, '-infinity'::timestamp without time zone)) AND (value_type = 'points'::transaction_value_type))
  • Rows Removed by Filter: 0
30.          

CTE txn

31. 0.191 78.914 ↑ 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=77.476..78.914 rows=1,188 loops=1)

32. 0.945 78.723 ↑ 8.4 1,188 1

Merge Full Join (cost=9,246.36..9,438.43 rows=9,982 width=128) (actual time=77.475..78.723 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))))
33.          

CTE most_recent_user_stmts

34. 1.266 16.561 ↑ 3.6 1,170 1

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

35. 8.744 15.295 ↑ 1.0 6,280 1

Sort (cost=1,180.28..1,195.98 rows=6,280 width=74) (actual time=14.004..15.295 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
36. 6.551 6.551 ↑ 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.770..6.551 rows=6,280 loops=1)

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

CTE txn_balances

38. 2.113 76.688 ↑ 26.1 382 1

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

  • Group Key: t_1.tenant_id, t_1.user_id, t_1.value_type, t_1.currency
39. 6.981 74.575 ↑ 12.0 878 1

Merge Right Join (cost=5,737.70..6,311.93 rows=10,535 width=65) (actual time=58.382..74.575 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
40. 0.506 17.471 ↑ 3.6 1,170 1

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

  • Sort Key: stmt.user_id
  • Sort Method: quicksort Memory: 103kB
41. 16.965 16.965 ↑ 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=14.015..16.965 rows=1,170 loops=1)

42. 23.043 50.123 ↓ 1.0 31,259 1

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

  • Sort Key: t_1.user_id
  • Sort Method: quicksort Memory: 3211kB
43. 27.080 27.080 ↓ 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.165..27.080 rows=31,259 loops=1)

  • Filter: ((tenant_id)::text = 'skirtsports'::text)
  • Rows Removed by Filter: 71328
44. 0.238 77.128 ↑ 26.1 382 1

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

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

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

46. 0.423 0.650 ↑ 3.6 1,170 1

Sort (cost=339.29..349.87 rows=4,229 width=56) (actual time=0.430..0.650 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
47. 0.227 0.227 ↑ 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.227 rows=1,170 loops=1)

48.          

SubPlan (forCTE Scan)

49. 0.250 81.720 ↑ 44.0 1 10

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

  • Group Key: txn.user_id
50. 81.470 81.470 ↑ 50.0 1 10

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

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