explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jrDZ

Settings
# exclusive inclusive rows x rows loops node
1. 6.192 75,568.381 ↓ 5,949.0 5,949 1

Sort (cost=2,547,968.27..2,547,968.27 rows=1 width=196) (actual time=75,566.954..75,568.381 rows=5,949 loops=1)

  • Sort Key: c.start DESC, c.month
  • Sort Method: quicksort Memory: 1773kB
2.          

CTE customers

3. 721.240 16,420.016 ↓ 537.9 107,584 1

HashAggregate (cost=698,805.31..698,807.31 rows=200 width=22) (actual time=16,348.245..16,420.016 rows=107,584 loops=1)

  • Group Key: s_1.username
  • Filter: (sum(t.net_total) >= '10'::double precision)
  • Rows Removed by Filter: 30236
4. 767.484 15,698.776 ↑ 1.0 789,037 1

Unique (cost=557,407.72..686,943.97 rows=790,756 width=1,303) (actual time=8,875.858..15,698.776 rows=789,037 loops=1)

5. 1,997.025 14,931.292 ↑ 1.0 1,146,982 1

Merge Join (cost=557,407.72..684,074.62 rows=1,147,742 width=1,303) (actual time=8,875.856..14,931.292 rows=1,146,982 loops=1)

  • Merge Cond: (t.delivery_id = s_1.delivery_id)
6. 497.089 497.089 ↑ 1.0 789,038 1

Index Scan using transaction_pkey on transaction t (cost=0.42..104,610.97 rows=790,756 width=33) (actual time=0.007..497.089 rows=789,038 loops=1)

7. 519.621 12,437.178 ↑ 1.0 1,147,021 1

Materialize (cost=557,401.28..563,139.99 rows=1,147,742 width=47) (actual time=8,875.840..12,437.178 rows=1,147,021 loops=1)

8. 10,317.231 11,917.557 ↑ 1.0 1,147,021 1

Sort (cost=557,401.28..560,270.63 rows=1,147,742 width=47) (actual time=8,875.829..11,917.557 rows=1,147,021 loops=1)

  • Sort Key: s_1.delivery_id
  • Sort Method: external merge Disk: 64064kB
9. 1,600.326 1,600.326 ↑ 1.0 1,147,021 1

Seq Scan on sale s_1 (cost=0.00..371,262.94 rows=1,147,742 width=47) (actual time=0.010..1,600.326 rows=1,147,021 loops=1)

  • Filter: (type = 'B2C'::text)
  • Rows Removed by Filter: 6302
10.          

CTE filtered_transactions

11. 1,353.332 39,426.011 ↓ 372.6 736,603 1

Hash Join (cost=934,016.72..1,083,376.24 rows=1,977 width=479) (actual time=29,260.526..39,426.011 rows=736,603 loops=1)

  • Hash Cond: (transaction_b2c_view.username = c_1.username)
12. 1,065.773 21,442.725 ↓ 198.1 783,456 1

Subquery Scan on transaction_b2c_view (cost=934,007.72..1,083,312.87 rows=3,954 width=479) (actual time=12,630.233..21,442.725 rows=783,456 loops=1)

  • Filter: ((transaction_b2c_view.created_date >= '2014-11-01'::date) AND (transaction_b2c_view.created_date < date_trunc('month'::text, (('now'::cstring)::date)::timestamp with time zone)))
  • Rows Removed by Filter: 5581
13. 1,073.636 20,376.952 ↑ 1.0 789,037 1

Unique (cost=934,007.72..1,063,543.97 rows=790,756 width=920) (actual time=12,630.209..20,376.952 rows=789,037 loops=1)

14. 2,164.952 19,303.316 ↑ 1.0 1,146,982 1

Merge Join (cost=934,007.72..1,060,674.62 rows=1,147,742 width=920) (actual time=12,630.205..19,303.316 rows=1,146,982 loops=1)

  • Merge Cond: (t_1.delivery_id = s_2.delivery_id)
15. 620.610 620.610 ↑ 1.0 789,038 1

Index Scan using transaction_pkey on transaction t_1 (cost=0.42..104,610.97 rows=790,756 width=73) (actual time=0.019..620.610 rows=789,038 loops=1)

16. 572.938 16,517.754 ↑ 1.0 1,147,021 1

Materialize (cost=934,001.28..939,739.99 rows=1,147,742 width=431) (actual time=12,630.176..16,517.754 rows=1,147,021 loops=1)

17. 14,182.538 15,944.816 ↑ 1.0 1,147,021 1

Sort (cost=934,001.28..936,870.63 rows=1,147,742 width=431) (actual time=12,630.171..15,944.816 rows=1,147,021 loops=1)

  • Sort Key: s_2.delivery_id
  • Sort Method: external merge Disk: 399160kB
18. 1,762.278 1,762.278 ↑ 1.0 1,147,021 1

Seq Scan on sale s_2 (cost=0.00..371,262.94 rows=1,147,742 width=431) (actual time=0.021..1,762.278 rows=1,147,021 loops=1)

  • Filter: (type = 'B2C'::text)
  • Rows Removed by Filter: 6302
19. 46.781 16,629.954 ↓ 537.9 107,583 1

Hash (cost=6.50..6.50 rows=200 width=32) (actual time=16,629.954..16,629.954 rows=107,583 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3868kB
20. 90.678 16,583.173 ↓ 537.9 107,584 1

HashAggregate (cost=4.50..6.50 rows=200 width=32) (actual time=16,550.919..16,583.173 rows=107,584 loops=1)

  • Group Key: c_1.username
21. 16,492.495 16,492.495 ↓ 537.9 107,584 1

CTE Scan on customers c_1 (cost=0.00..4.00 rows=200 width=32) (actual time=16,348.247..16,492.495 rows=107,584 loops=1)

22.          

CTE first_activity

23. 976.554 42,221.634 ↓ 541.7 108,349 1

HashAggregate (cost=64.13..66.63 rows=200 width=136) (actual time=42,131.567..42,221.634 rows=108,349 loops=1)

  • Group Key: filtered_transactions.username, filtered_transactions.brand, filtered_transactions.city, filtered_transactions.attribution_channel
24. 41,245.080 41,245.080 ↓ 374.5 736,603 1

CTE Scan on filtered_transactions (cost=0.00..39.54 rows=1,967 width=136) (actual time=29,260.530..41,245.080 rows=736,603 loops=1)

  • Filter: (username IS NOT NULL)
25.          

CTE activities

26. 176.528 73,279.916 ↓ 265,993.0 265,993 1

Unique (cost=765,717.60..765,717.89 rows=1 width=154) (actual time=71,032.182..73,279.916 rows=265,993 loops=1)

27. 620.172 73,103.388 ↓ 266,290.0 266,290 1

GroupAggregate (cost=765,717.60..765,717.89 rows=1 width=154) (actual time=71,032.180..73,103.388 rows=266,290 loops=1)

  • Group Key: s_1_1.username, (date_trunc('month'::text, s_1_1.created_date)), (date_trunc('month'::text, f.created_date)), (month_diff(s_1_1.created_date, f.created_date))
28. 4,569.019 72,483.216 ↓ 736,606.0 736,606 1

Sort (cost=765,717.60..765,717.60 rows=1 width=90) (actual time=71,032.168..72,483.216 rows=736,606 loops=1)

  • Sort Key: s_1_1.username, (date_trunc('month'::text, s_1_1.created_date)), (date_trunc('month'::text, f.created_date)), (month_diff(s_1_1.created_date, f.created_date))
  • Sort Method: external merge Disk: 76600kB
29. 7,119.996 67,914.197 ↓ 736,606.0 736,606 1

Hash Join (cost=596,643.22..765,717.59 rows=1 width=90) (actual time=52,394.609..67,914.197 rows=736,606 loops=1)

  • Hash Cond: ((s_1_1.username = f.username) AND (s_1_1.city = f.city) AND (s_1_1.brand = f.brand))
  • Join Filter: ((s_1_1.attribution_channel = f.attribution_channel) OR ((s_1_1.attribution_channel IS NULL) AND (f.attribution_channel IS NULL)))
  • Rows Removed by Join Filter: 38698
30. 969.216 18,407.088 ↓ 3.0 783,461 1

Subquery Scan on s_1_1 (cost=596,635.72..743,963.98 rows=263,585 width=78) (actual time=10,007.029..18,407.088 rows=783,461 loops=1)

  • Filter: (s_1_1.created_date < date_trunc('month'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Rows Removed by Filter: 5576
31. 791.166 17,437.872 ↑ 1.0 789,037 1

Unique (cost=596,635.72..726,171.97 rows=790,756 width=1,239) (actual time=10,007.006..17,437.872 rows=789,037 loops=1)

32. 2,119.333 16,646.706 ↑ 1.0 1,146,982 1

Merge Join (cost=596,635.72..723,302.62 rows=1,147,742 width=1,239) (actual time=10,007.003..16,646.706 rows=1,146,982 loops=1)

  • Merge Cond: (t_2.delivery_id = s_3.delivery_id)
33. 585.544 585.544 ↑ 1.0 789,038 1

Index Scan using transaction_pkey on transaction t_2 (cost=0.42..104,610.97 rows=790,756 width=41) (actual time=0.019..585.544 rows=789,038 loops=1)

34. 526.699 13,941.829 ↑ 1.0 1,147,021 1

Materialize (cost=596,629.28..602,367.99 rows=1,147,742 width=87) (actual time=10,006.973..13,941.829 rows=1,147,021 loops=1)

35. 11,751.873 13,415.130 ↑ 1.0 1,147,021 1

Sort (cost=596,629.28..599,498.63 rows=1,147,742 width=87) (actual time=10,006.970..13,415.130 rows=1,147,021 loops=1)

  • Sort Key: s_3.delivery_id
  • Sort Method: external merge Disk: 105656kB
36. 1,663.257 1,663.257 ↑ 1.0 1,147,021 1

Seq Scan on sale s_3 (cost=0.00..371,262.94 rows=1,147,742 width=87) (actual time=0.015..1,663.257 rows=1,147,021 loops=1)

  • Filter: (type = 'B2C'::text)
  • Rows Removed by Filter: 6302
37. 67.924 42,387.113 ↓ 541.7 108,349 1

Hash (cost=4.00..4.00 rows=200 width=136) (actual time=42,387.113..42,387.113 rows=108,349 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3585kB
38. 42,319.189 42,319.189 ↓ 541.7 108,349 1

CTE Scan on first_activity f (cost=0.00..4.00 rows=200 width=136) (actual time=42,131.572..42,319.189 rows=108,349 loops=1)

39.          

CTE cohorts

40. 137.113 74,222.541 ↓ 5,951.0 5,951 1

GroupAggregate (cost=0.03..0.07 rows=1 width=132) (actual time=73,833.309..74,222.541 rows=5,951 loops=1)

  • Group Key: activities.start, activities.month, activities.brand, activities.city, activities.attribution_channel
41. 602.600 74,085.428 ↓ 265,993.0 265,993 1

Sort (cost=0.03..0.04 rows=1 width=156) (actual time=73,833.304..74,085.428 rows=265,993 loops=1)

  • Sort Key: activities.start, activities.month, activities.brand, activities.city, activities.attribution_channel
  • Sort Method: external merge Disk: 23232kB
42. 73,482.828 73,482.828 ↓ 265,993.0 265,993 1

CTE Scan on activities (cost=0.00..0.02 rows=1 width=156) (actual time=71,032.185..73,482.828 rows=265,993 loops=1)

43.          

CTE start_size

44. 393.204 393.204 ↓ 409.0 409 1

CTE Scan on cohorts (cost=0.00..0.02 rows=1 width=128) (actual time=0.003..393.204 rows=409 loops=1)

  • Filter: (month = 0)
  • Rows Removed by Filter: 5542
45. 727.032 75,562.189 ↓ 5,949.0 5,949 1

Nested Loop (cost=0.00..0.10 rows=1 width=196) (actual time=73,833.354..75,562.189 rows=5,949 loops=1)

  • Join Filter: (((s.attribution_channel = c.attribution_channel) OR ((s.attribution_channel IS NULL) AND (c.attribution_channel IS NULL))) AND (c.start = s.start) AND (c.city = s.city) AND (c.brand = s.brand))
  • Rows Removed by Join Filter: 2427192
46. 73,835.725 73,835.725 ↓ 5,949.0 5,949 1

CTE Scan on cohorts c (cost=0.00..0.02 rows=1 width=132) (actual time=73,833.326..73,835.725 rows=5,949 loops=1)

  • Filter: (month >= 0)
  • Rows Removed by Filter: 2
47. 999.432 999.432 ↓ 409.0 409 5,949

CTE Scan on start_size s (cost=0.00..0.02 rows=1 width=128) (actual time=0.000..0.168 rows=409 loops=5,949)