explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8RNK : amplitude companies 2 subqueries with aggregation but without window fn

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 22,491.379 1,999,533.669 ↑ 2.2 3,881,979 1

Hash Join (cost=15,283,785.21..17,195,626.76 rows=8,414,544 width=234) (actual time=1,965,815.552..1,999,533.669 rows=3,881,979 loops=1)

  • Hash Cond: (ampl.company_key = dc.company_key)
2. 1,770.707 1,976,950.998 ↑ 2.2 3,882,327 1

Subquery Scan on ampl (cost=15,266,517.63..16,528,699.23 rows=8,414,544 width=234) (actual time=1,965,723.992..1,976,950.998 rows=3,882,327 loops=1)

3. 3,933.038 1,975,180.291 ↑ 2.2 3,882,327 1

GroupAggregate (cost=15,266,517.63..16,444,553.79 rows=8,414,544 width=242) (actual time=1,965,723.991..1,975,180.291 rows=3,882,327 loops=1)

  • Group Key: fact_amplitude_events.amplitude_id, (timezone('UTC'::text, fact_amplitude_events.client_event_time)), (CASE WHEN (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) THEN 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 ELSE foo.company_key END), (CASE WHEN ((fact_amplitude_events.user_properties ->> 'user_id'::text) IS NOT NULL) THEN ((fact_amplitude_events.user_properties ->> 'user_id'::text))::integer ELSE foo_1.user_key END), (((fact_amplitude_events.user_properties)::json ->> 'utm_source'::text)), (((fact_amplitude_events.user_properties)::json ->> 'utm_campaign'::text)), (((fact_amplitude_events.user_properties)::json ->> 'utm_content'::text)), (((fact_amplitude_events.user_properties)::json ->> 'utm_medium'::text)), (((fact_amplitude_events.user_properties)::json ->> 'utm_term'::text)), (((fact_amplitude_events.user_properties)::json ->> 'referrer'::text)), fact_amplitude_events.country, fact_amplitude_events.city
  • Filter: ((timezone('UTC'::text, fact_amplitude_events.client_event_time)) = min((timezone('UTC'::text, fact_amplitude_events.client_event_time))))
4. 192,081.602 1,971,247.253 ↑ 2.1 3,960,861 1

Sort (cost=15,266,517.63..15,287,553.99 rows=8,414,544 width=242) (actual time=1,965,723.976..1,971,247.253 rows=3,960,861 loops=1)

  • Sort Key: fact_amplitude_events.amplitude_id, (timezone('UTC'::text, fact_amplitude_events.client_event_time)), (CASE WHEN (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) THEN 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 ELSE foo.company_key END), (CASE WHEN ((fact_amplitude_events.user_properties ->> 'user_id'::text) IS NOT NULL) THEN ((fact_amplitude_events.user_properties ->> 'user_id'::text))::integer ELSE foo_1.user_key END), (((fact_amplitude_events.user_properties)::json ->> 'utm_source'::text)), (((fact_amplitude_events.user_properties)::json ->> 'utm_campaign'::text)), (((fact_amplitude_events.user_properties)::json ->> 'utm_content'::text)), (((fact_amplitude_events.user_properties)::json ->> 'utm_medium'::text)), (((fact_amplitude_events.user_properties)::json ->> 'utm_term'::text)), (((fact_amplitude_events.user_properties)::json ->> 'referrer'::text)), fact_amplitude_events.country, fact_amplitude_events.city
  • Sort Method: external merge Disk: 768880kB
5. 320,971.249 1,779,165.651 ↑ 2.1 3,960,861 1

Hash Left Join (cost=1,785,590.95..11,365,062.69 rows=8,414,544 width=242) (actual time=497,236.252..1,779,165.651 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
6. 960,819.509 1,457,533.360 ↑ 2.1 3,962,893 1

Hash Right Join (cost=1,747,258.91..5,814,490.42 rows=8,414,544 width=480) (actual time=496,575.015..1,457,533.360 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
7. 69.042 615.285 ↓ 182.4 153,586 1

Subquery Scan on foo (cost=32,847.67..38,321.51 rows=842 width=8) (actual time=369.597..615.285 rows=153,586 loops=1)

  • Filter: (foo.rk = 1)
  • Rows Removed by Filter: 8014
8. 125.799 546.243 ↑ 1.0 161,600 1

WindowAgg (cost=32,847.67..36,216.19 rows=168,426 width=24) (actual time=369.594..546.243 rows=161,600 loops=1)

9. 138.872 420.444 ↑ 1.0 161,600 1

Sort (cost=32,847.67..33,268.73 rows=168,426 width=16) (actual time=369.587..420.444 rows=161,600 loops=1)

  • Sort Key: fcur.company_key, fcur.fcur_created_at
  • Sort Method: external merge Disk: 4752kB
10. 122.588 281.572 ↑ 1.0 161,600 1

Hash Join (cost=8,121.22..15,346.31 rows=168,426 width=16) (actual time=108.801..281.572 rows=161,600 loops=1)

  • Hash Cond: (fcur.user_key = du.user_key)
11. 51.619 51.619 ↑ 1.0 168,640 1

Seq Scan on fact_company_user_roles fcur (cost=0.00..4,512.40 rows=168,640 width=16) (actual time=0.085..51.619 rows=168,640 loops=1)

12. 50.416 107.365 ↓ 1.0 159,235 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 2431kB
13. 56.949 56.949 ↓ 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.032..56.949 rows=159,235 loops=1)

  • Filter: (NOT is_agent)
  • Rows Removed by Filter: 176
14. 37,805.750 496,098.566 ↓ 1.0 8,418,465 1

Hash (cost=1,091,537.44..1,091,537.44 rows=8,414,544 width=476) (actual time=496,098.565..496,098.566 rows=8,418,465 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 4096 (originally 2048) Memory Usage: 2875019kB
15. 458,292.816 458,292.816 ↓ 1.0 8,418,465 1

Seq Scan on fact_amplitude_events (cost=0.00..1,091,537.44 rows=8,414,544 width=476) (actual time=0.007..458,292.816 rows=8,418,465 loops=1)

16. 49.170 661.042 ↓ 182.4 153,586 1

Hash (cost=38,321.51..38,321.51 rows=842 width=8) (actual time=661.041..661.042 rows=153,586 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 2 (originally 1) Memory Usage: 4024kB
17. 67.838 611.872 ↓ 182.4 153,586 1

Subquery Scan on foo_1 (cost=32,847.67..38,321.51 rows=842 width=8) (actual time=372.911..611.872 rows=153,586 loops=1)

  • Filter: (foo_1.rk = 1)
  • Rows Removed by Filter: 8014
18. 124.023 544.034 ↑ 1.0 161,600 1

WindowAgg (cost=32,847.67..36,216.19 rows=168,426 width=24) (actual time=372.909..544.034 rows=161,600 loops=1)

19. 133.811 420.011 ↑ 1.0 161,600 1

Sort (cost=32,847.67..33,268.73 rows=168,426 width=16) (actual time=372.903..420.011 rows=161,600 loops=1)

  • Sort Key: fcur_1.company_key, fcur_1.fcur_created_at
  • Sort Method: external merge Disk: 4752kB
20. 122.433 286.200 ↑ 1.0 161,600 1

Hash Join (cost=8,121.22..15,346.31 rows=168,426 width=16) (actual time=114.890..286.200 rows=161,600 loops=1)

  • Hash Cond: (fcur_1.user_key = du_1.user_key)
21. 50.900 50.900 ↑ 1.0 168,640 1

Seq Scan on fact_company_user_roles fcur_1 (cost=0.00..4,512.40 rows=168,640 width=16) (actual time=0.095..50.900 rows=168,640 loops=1)

22. 53.149 112.867 ↓ 1.0 159,235 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 2431kB
23. 59.718 59.718 ↓ 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.034..59.718 rows=159,235 loops=1)

  • Filter: (NOT is_agent)
  • Rows Removed by Filter: 176
24. 48.890 91.292 ↑ 1.8 154,305 1

Hash (cost=12,725.05..12,725.05 rows=276,842 width=4) (actual time=91.292..91.292 rows=154,305 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2379kB
25. 42.402 42.402 ↑ 1.8 154,305 1

Index Only Scan using dim_companies_pkey on dim_companies dc (cost=0.42..12,725.05 rows=276,842 width=4) (actual time=0.024..42.402 rows=154,305 loops=1)

  • Heap Fetches: 32
Planning time : 1.067 ms
Execution time : 2,001,037.785 ms