explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yrwX : amplitude companies 2 subqueries with aggregation

Settings
# exclusive inclusive rows x rows loops node
1. 5,264.932 1,851,590.908 ↓ 2.2 94,161 1

Hash Join (cost=27,501,703.00..27,698,592.34 rows=42,044 width=508) (actual time=1,676,458.903..1,851,590.908 rows=94,161 loops=1)

  • Hash Cond: (ampl.company_key = dc.company_key)
2.          

CTE events_all

3. 147,095.640 1,838,913.898 ↑ 2.1 3,960,861 1

WindowAgg (cost=26,238,537.33..27,478,825.74 rows=8,408,735 width=250) (actual time=1,675,690.167..1,838,913.898 rows=3,960,861 loops=1)

4. 272,755.532 1,691,818.258 ↑ 2.1 3,960,861 1

Sort (cost=26,238,537.33..26,259,559.17 rows=8,408,735 width=495) (actual time=1,675,690.058..1,691,818.258 rows=3,960,861 loops=1)

  • Sort Key: foo.company_key, (timezone('UTC'::text, fact_amplitude_events.client_event_time))
  • Sort Method: external merge Disk: 2231896kB
5. 2,788.371 1,419,062.726 ↑ 2.1 3,960,861 1

WindowAgg (cost=19,330,579.05..19,666,928.45 rows=8,408,735 width=495) (actual time=1,414,245.690..1,419,062.726 rows=3,960,861 loops=1)

6. 113,562.347 1,416,274.355 ↑ 2.1 3,960,861 1

Sort (cost=19,330,579.05..19,351,600.88 rows=8,408,735 width=487) (actual time=1,414,245.681..1,416,274.355 rows=3,960,861 loops=1)

  • Sort Key: (CASE WHEN ((fact_amplitude_events.event_properties ->> 'company_id'::text) ~ '^\d+$'::text) THEN ((fact_amplitude_events.event_properties ->> 'company_id'::text))::integer WHEN ((fact_amplitude_events.event_properties ->> 'company_id'::text) !~ '^\d+$'::text) THEN NULL::integer ELSE NULL::integer END), (timezone('UTC'::text, fact_amplitude_events.client_event_time))
  • Sort Method: external merge Disk: 2188928kB
7. 21,407.142 1,302,712.008 ↑ 2.1 3,960,861 1

Hash Left Join (cost=1,803,612.96..12,845,196.16 rows=8,408,735 width=487) (actual time=1,076,371.717..1,302,712.008 rows=3,960,861 loops=1)

  • Hash Cond: (CASE WHEN ((fact_amplitude_events.event_properties ->> 'company_id'::text) ~ '^\d+$'::text) THEN ((fact_amplitude_events.event_properties ->> 'company_id'::text))::integer WHEN ((fact_amplitude_events.event_properties ->> 'company_id'::text) !~ '^\d+$'::text) THEN NULL::integer ELSE NULL::integer END = foo_1.company_key)
  • Filter: (((fact_amplitude_events.user_properties ->> 'user_id'::text) IS NOT NULL) OR (foo_1.user_key IS NOT NULL))
  • Rows Removed by Filter: 2032
8. 204,782.281 1,280,644.192 ↑ 2.1 3,962,893 1

Hash Right Join (cost=1,755,330.48..6,720,888.94 rows=8,408,735 width=479) (actual time=1,075,710.932..1,280,644.192 rows=3,962,893 loops=1)

  • Hash Cond: (foo.user_key = ((fact_amplitude_events.user_properties ->> 'user_id'::text))::integer)
  • Filter: ((CASE WHEN ((fact_amplitude_events.event_properties ->> 'company_id'::text) ~ '^\d+$'::text) THEN ((fact_amplitude_events.event_properties ->> 'company_id'::text))::integer WHEN ((fact_amplitude_events.event_properties ->> 'company_id'::text) !~ '^\d+$'::text) THEN NULL::integer ELSE NULL::integer END IS NOT NULL) OR (foo.company_key IS NOT NULL))
  • Rows Removed by Filter: 4928701
9. 74.858 623.465 ↓ 145.4 153,586 1

Subquery Scan on foo (cost=41,406.94..48,269.28 rows=1,056 width=8) (actual time=365.732..623.465 rows=153,586 loops=1)

  • Filter: (foo.rk = 1)
  • Rows Removed by Filter: 8014
10. 130.992 548.607 ↑ 1.3 161,600 1

WindowAgg (cost=41,406.94..45,629.92 rows=211,149 width=24) (actual time=365.729..548.607 rows=161,600 loops=1)

11. 138.307 417.615 ↑ 1.3 161,600 1

Sort (cost=41,406.94..41,934.81 rows=211,149 width=16) (actual time=365.723..417.615 rows=161,600 loops=1)

  • Sort Key: fcur.company_key, fcur.fcur_created_at
  • Sort Method: external merge Disk: 4752kB
12. 120.490 279.308 ↑ 1.3 161,600 1

Hash Join (cost=8,121.22..19,121.03 rows=211,149 width=16) (actual time=108.784..279.308 rows=161,600 loops=1)

  • Hash Cond: (fcur.user_key = du.user_key)
13. 51.463 51.463 ↑ 2.0 168,640 1

Seq Scan on fact_company_user_roles fcur (cost=0.00..6,198.51 rows=337,251 width=16) (actual time=0.094..51.463 rows=168,640 loops=1)

14. 50.528 107.355 ↓ 1.0 159,235 1

Hash (cost=5,509.11..5,509.11 rows=159,209 width=4) (actual time=107.355..107.355 rows=159,235 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2431kB
15. 56.827 56.827 ↓ 1.0 159,235 1

Seq Scan on dim_users du (cost=0.00..5,509.11 rows=159,209 width=4) (actual time=0.037..56.827 rows=159,235 loops=1)

  • Filter: (NOT is_agent)
  • Rows Removed by Filter: 176
16. 102,627.618 1,075,238.446 ↓ 1.0 8,418,465 1

Hash (cost=1,091,479.35..1,091,479.35 rows=8,408,735 width=475) (actual time=1,075,238.445..1,075,238.446 rows=8,418,465 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 4096 (originally 2048) Memory Usage: 2875019kB
17. 972,610.828 972,610.828 ↓ 1.0 8,418,465 1

Seq Scan on fact_amplitude_events (cost=0.00..1,091,479.35 rows=8,408,735 width=475) (actual time=0.006..972,610.828 rows=8,418,465 loops=1)

18. 48.911 660.674 ↓ 145.4 153,586 1

Hash (cost=48,269.28..48,269.28 rows=1,056 width=8) (actual time=660.674..660.674 rows=153,586 loops=1)

  • Buckets: 131072 (originally 2048) Batches: 2 (originally 1) Memory Usage: 4024kB
19. 68.399 611.763 ↓ 145.4 153,586 1

Subquery Scan on foo_1 (cost=41,406.94..48,269.28 rows=1,056 width=8) (actual time=367.796..611.763 rows=153,586 loops=1)

  • Filter: (foo_1.rk = 1)
  • Rows Removed by Filter: 8014
20. 127.832 543.364 ↑ 1.3 161,600 1

WindowAgg (cost=41,406.94..45,629.92 rows=211,149 width=24) (actual time=367.795..543.364 rows=161,600 loops=1)

21. 134.149 415.532 ↑ 1.3 161,600 1

Sort (cost=41,406.94..41,934.81 rows=211,149 width=16) (actual time=367.790..415.532 rows=161,600 loops=1)

  • Sort Key: fcur_1.company_key, fcur_1.fcur_created_at
  • Sort Method: external merge Disk: 4752kB
22. 120.678 281.383 ↑ 1.3 161,600 1

Hash Join (cost=8,121.22..19,121.03 rows=211,149 width=16) (actual time=110.439..281.383 rows=161,600 loops=1)

  • Hash Cond: (fcur_1.user_key = du_1.user_key)
23. 51.707 51.707 ↑ 2.0 168,640 1

Seq Scan on fact_company_user_roles fcur_1 (cost=0.00..6,198.51 rows=337,251 width=16) (actual time=0.091..51.707 rows=168,640 loops=1)

24. 51.067 108.998 ↓ 1.0 159,235 1

Hash (cost=5,509.11..5,509.11 rows=159,209 width=4) (actual time=108.998..108.998 rows=159,235 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2431kB
25. 57.931 57.931 ↓ 1.0 159,235 1

Seq Scan on dim_users du_1 (cost=0.00..5,509.11 rows=159,209 width=4) (actual time=0.035..57.931 rows=159,235 loops=1)

  • Filter: (NOT is_agent)
  • Rows Removed by Filter: 176
26. 1,846,227.055 1,846,227.055 ↓ 2.2 94,169 1

CTE Scan on events_all ampl (cost=0.00..189,196.54 rows=42,044 width=508) (actual time=1,675,690.172..1,846,227.055 rows=94,169 loops=1)

  • Filter: (rn2 = 1)
  • Rows Removed by Filter: 3866692
27. 48.649 98.921 ↑ 2.8 154,305 1

Hash (cost=15,907.67..15,907.67 rows=424,767 width=4) (actual time=98.920..98.921 rows=154,305 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 1702kB
28. 50.272 50.272 ↑ 2.8 154,305 1

Seq Scan on dim_companies dc (cost=0.00..15,907.67 rows=424,767 width=4) (actual time=0.028..50.272 rows=154,305 loops=1)

Planning time : 1.063 ms
Execution time : 1,852,655.132 ms