explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8TI8 : Optimization for: Optimization for: amplitude companies 2 subqueries with aggregation but without window fn; plan #8RNK; plan #3iqr

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 97.637 435,506.523 ↓ 404.3 85,300 1

Nested Loop (cost=6,037,496.87..6,039,348.57 rows=211 width=508) (actual time=435,085.702..435,506.523 rows=85,300 loops=1)

2.          

CTE first_c

3. 520.872 188,309.176 ↓ 2.0 83,677 1

Subquery Scan on events_c (cost=2,245,514.13..3,084,144.53 rows=41,932 width=238) (actual time=123,803.912..188,309.176 rows=83,677 loops=1)

  • Filter: (events_c.rn = 1)
  • Rows Removed by Filter: 1856798
4. 63,372.875 187,788.304 ↑ 4.3 1,940,475 1

WindowAgg (cost=2,245,514.13..2,979,315.73 rows=8,386,304 width=270) (actual time=123,803.910..187,788.304 rows=1,940,475 loops=1)

5. 8,551.150 124,415.429 ↑ 4.3 1,940,475 1

Sort (cost=2,245,514.13..2,266,479.89 rows=8,386,304 width=508) (actual time=123,803.825..124,415.429 rows=1,940,475 loops=1)

  • Sort Key: ((fact_amplitude_events.event_properties ->> 'company_id'::text)), (timezone('UTC'::text, fact_amplitude_events.client_event_time))
  • Sort Method: quicksort Memory: 1421646kB
6. 115,864.279 115,864.279 ↑ 4.3 1,940,475 1

Seq Scan on fact_amplitude_events (cost=0.00..1,281,105.79 rows=8,386,304 width=508) (actual time=0.046..115,864.279 rows=1,940,475 loops=1)

  • Filter: (CASE WHEN ((event_properties ->> 'company_id'::text) ~ '^\d+$'::text) THEN ((event_properties ->> 'company_id'::text))::integer WHEN ((event_properties ->> 'company_id'::text) !~ '^\d+$'::text) THEN NULL::integer ELSE NULL::integer END IS NOT NULL)
  • Rows Removed by Filter: 6477990
7.          

CTE add_users

8. 92.577 189,102.889 ↓ 2.0 83,677 1

Merge Right Join (cost=31,149.01..36,839.44 rows=41,932 width=516) (actual time=188,765.052..189,102.889 rows=83,677 loops=1)

  • Merge Cond: (foo.company_key = first_c.company_key)
9. 64.235 549.223 ↓ 182.5 153,640 1

Subquery Scan on foo (cost=27,090.88..32,566.70 rows=842 width=8) (actual time=321.512..549.223 rows=153,640 loops=1)

  • Filter: (foo.rk = 1)
  • Rows Removed by Filter: 8015
10. 128.127 484.988 ↑ 1.0 161,655 1

WindowAgg (cost=27,090.88..30,460.62 rows=168,487 width=24) (actual time=321.510..484.988 rows=161,655 loops=1)

11. 113.158 356.861 ↑ 1.0 161,655 1

Sort (cost=27,090.88..27,512.09 rows=168,487 width=16) (actual time=321.503..356.861 rows=161,655 loops=1)

  • Sort Key: fcur.company_key, fcur.fcur_created_at
  • Sort Method: quicksort Memory: 18774kB
12. 98.926 243.703 ↑ 1.0 161,655 1

Hash Join (cost=7,506.50..12,464.29 rows=168,487 width=16) (actual time=107.059..243.703 rows=161,655 loops=1)

  • Hash Cond: (fcur.user_key = du.user_key)
13. 38.000 38.000 ↑ 1.0 168,695 1

Seq Scan on fact_company_user_roles fcur (cost=0.00..4,514.95 rows=168,695 width=16) (actual time=0.004..38.000 rows=168,695 loops=1)

14. 48.400 106.777 ↓ 1.0 159,289 1

Hash (cost=5,515.65..5,515.65 rows=159,268 width=4) (actual time=106.777..106.777 rows=159,289 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 7649kB
15. 58.377 58.377 ↓ 1.0 159,289 1

Seq Scan on dim_users du (cost=0.00..5,515.65 rows=159,268 width=4) (actual time=0.004..58.377 rows=159,289 loops=1)

  • Filter: (NOT is_agent)
  • Rows Removed by Filter: 176
16. 59.208 188,461.089 ↓ 2.0 83,677 1

Sort (cost=4,058.13..4,162.96 rows=41,932 width=512) (actual time=188,443.533..188,461.089 rows=83,677 loops=1)

  • Sort Key: first_c.company_key
  • Sort Method: quicksort Memory: 26955kB
17. 188,401.881 188,401.881 ↓ 2.0 83,677 1

CTE Scan on first_c (cost=0.00..838.64 rows=41,932 width=512) (actual time=123,803.915..188,401.881 rows=83,677 loops=1)

18.          

CTE first_u

19. 529.856 244,919.735 ↑ 2.2 19,265 1

Subquery Scan on events_u (cost=2,119,087.44..2,873,854.80 rows=41,932 width=238) (actual time=154,761.939..244,919.735 rows=19,265 loops=1)

  • Filter: (events_u.rn = 1)
  • Rows Removed by Filter: 2169782
20. 88,916.299 244,389.879 ↑ 3.8 2,189,047 1

WindowAgg (cost=2,119,087.44..2,769,026.00 rows=8,386,304 width=270) (actual time=154,761.937..244,389.879 rows=2,189,047 loops=1)

21. 8,027.773 155,473.580 ↑ 3.8 2,189,047 1

Sort (cost=2,119,087.44..2,140,053.20 rows=8,386,304 width=457) (actual time=154,761.839..155,473.580 rows=2,189,047 loops=1)

  • Sort Key: ((fact_amplitude_events_1.user_properties ->> 'user_id'::text)), (timezone('UTC'::text, fact_amplitude_events_1.client_event_time))
  • Sort Method: quicksort Memory: 1781047kB
22. 147,445.807 147,445.807 ↑ 3.8 2,189,047 1

Seq Scan on fact_amplitude_events fact_amplitude_events_1 (cost=0.00..1,154,679.09 rows=8,386,304 width=457) (actual time=0.021..147,445.807 rows=2,189,047 loops=1)

  • Filter: ((user_properties ->> 'user_id'::text) IS NOT NULL)
  • Rows Removed by Filter: 6229418
23.          

CTE add_comps

24. 47.594 245,647.934 ↓ 69.5 19,247 1

Merge Join (cost=36,665.74..36,882.38 rows=277 width=516) (actual time=245,565.690..245,647.934 rows=19,247 loops=1)

  • Merge Cond: (first_u.user_key = foo_1.user_key)
25. 13.815 244,956.614 ↑ 2.2 19,265 1

Sort (cost=4,058.13..4,162.96 rows=41,932 width=512) (actual time=244,952.584..244,956.614 rows=19,265 loops=1)

  • Sort Key: first_u.user_key
  • Sort Method: quicksort Memory: 6049kB
26. 244,942.799 244,942.799 ↑ 2.2 19,265 1

CTE Scan on first_u (cost=0.00..838.64 rows=41,932 width=512) (actual time=154,761.942..244,942.799 rows=19,265 loops=1)

27. 90.429 643.726 ↓ 182.2 153,402 1

Sort (cost=32,607.61..32,609.72 rows=842 width=8) (actual time=613.096..643.726 rows=153,402 loops=1)

  • Sort Key: foo_1.user_key
  • Sort Method: quicksort Memory: 13346kB
28. 68.209 553.297 ↓ 182.5 153,640 1

Subquery Scan on foo_1 (cost=27,090.88..32,566.70 rows=842 width=8) (actual time=325.397..553.297 rows=153,640 loops=1)

  • Filter: (foo_1.rk = 1)
  • Rows Removed by Filter: 8015
29. 125.350 485.088 ↑ 1.0 161,655 1

WindowAgg (cost=27,090.88..30,460.62 rows=168,487 width=24) (actual time=325.396..485.088 rows=161,655 loops=1)

30. 113.864 359.738 ↑ 1.0 161,655 1

Sort (cost=27,090.88..27,512.09 rows=168,487 width=16) (actual time=325.385..359.738 rows=161,655 loops=1)

  • Sort Key: fcur_1.company_key, fcur_1.fcur_created_at
  • Sort Method: quicksort Memory: 18774kB
31. 102.177 245.874 ↑ 1.0 161,655 1

Hash Join (cost=7,506.50..12,464.29 rows=168,487 width=16) (actual time=106.053..245.874 rows=161,655 loops=1)

  • Hash Cond: (fcur_1.user_key = du_1.user_key)
32. 37.942 37.942 ↑ 1.0 168,695 1

Seq Scan on fact_company_user_roles fcur_1 (cost=0.00..4,514.95 rows=168,695 width=16) (actual time=0.007..37.942 rows=168,695 loops=1)

33. 48.176 105.755 ↓ 1.0 159,289 1

Hash (cost=5,515.65..5,515.65 rows=159,268 width=4) (actual time=105.755..105.755 rows=159,289 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 7649kB
34. 57.579 57.579 ↓ 1.0 159,289 1

Seq Scan on dim_users du_1 (cost=0.00..5,515.65 rows=159,268 width=4) (actual time=0.005..57.579 rows=159,289 loops=1)

  • Filter: (NOT is_agent)
  • Rows Removed by Filter: 176
35.          

CTE together

36. 41.820 434,905.559 ↓ 2.4 102,924 1

Append (cost=0.00..844.18 rows=42,209 width=508) (actual time=188,765.056..434,905.559 rows=102,924 loops=1)

37. 189,193.403 189,193.403 ↓ 2.0 83,677 1

CTE Scan on add_users (cost=0.00..838.64 rows=41,932 width=508) (actual time=188,765.055..189,193.403 rows=83,677 loops=1)

38. 245,670.336 245,670.336 ↓ 69.5 19,247 1

CTE Scan on add_comps (cost=0.00..5.54 rows=277 width=508) (actual time=245,565.694..245,670.336 rows=19,247 loops=1)

39.          

CTE first_t

40. 106.460 435,214.420 ↓ 2.4 102,924 1

WindowAgg (cost=4,086.94..4,931.12 rows=42,209 width=516) (actual time=435,085.663..435,214.420 rows=102,924 loops=1)

41. 92.857 435,107.960 ↓ 2.4 102,924 1

Sort (cost=4,086.94..4,192.46 rows=42,209 width=508) (actual time=435,085.656..435,107.960 rows=102,924 loops=1)

  • Sort Key: together.company_key, together.client_event_time
  • Sort Method: quicksort Memory: 32252kB
42. 435,015.103 435,015.103 ↓ 2.4 102,924 1

CTE Scan on together (cost=0.00..844.18 rows=42,209 width=508) (actual time=188,765.059..435,015.103 rows=102,924 loops=1)

43. 435,323.571 435,323.571 ↓ 404.3 85,315 1

CTE Scan on first_t ampl (cost=0.00..949.70 rows=211 width=508) (actual time=435,085.666..435,323.571 rows=85,315 loops=1)

  • Filter: (rn2 = 1)
  • Rows Removed by Filter: 17609
44. 85.315 85.315 ↑ 1.0 1 85,315

Index Only Scan using dim_companies_pkey on dim_companies dc (cost=0.42..4.27 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=85,315)

  • Index Cond: (company_key = ampl.company_key)
  • Heap Fetches: 0
Planning time : 1.245 ms
Execution time : 435,559.856 ms