explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 25,151.061 1,784,524.830 ↑ 2.2 3,881,979 1

Hash Join (cost=14,090,305.99..16,004,825.64 rows=8,428,446 width=234) (actual time=1,743,837.599..1,784,524.830 rows=3,881,979 loops=1)

  • Hash Cond: (ampl.company_key = dc.company_key)
2. 1,690.040 1,759,278.544 ↑ 2.2 3,882,327 1

Subquery Scan on ampl (cost=14,077,846.14..15,342,113.04 rows=8,428,446 width=234) (actual time=1,743,742.224..1,759,278.544 rows=3,882,327 loops=1)

3. 3,821.131 1,757,588.504 ↑ 2.2 3,882,327 1

GroupAggregate (cost=14,077,846.14..15,257,828.58 rows=8,428,446 width=242) (actual time=1,743,742.223..1,757,588.504 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. 176,959.695 1,753,767.373 ↑ 2.1 3,960,861 1

Sort (cost=14,077,846.14..14,098,917.26 rows=8,428,446 width=242) (actual time=1,743,742.208..1,753,767.373 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: 768856kB
5. 681,305.102 1,576,807.678 ↑ 2.1 3,960,861 1

Hash Right Join (cost=8,392,730.87..10,169,851.30 rows=8,428,446 width=242) (actual time=896,329.445..1,576,807.678 rows=3,960,861 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: 17016
6. 69.320 3,093.395 ↓ 182.2 153,630 1

Subquery Scan on foo (cost=32,865.85..38,342.82 rows=843 width=8) (actual time=2,845.580..3,093.395 rows=153,630 loops=1)

  • Filter: (foo.rk = 1)
  • Rows Removed by Filter: 8015
7. 127.408 3,024.075 ↑ 1.0 161,645 1

WindowAgg (cost=32,865.85..36,236.29 rows=168,522 width=24) (actual time=2,845.577..3,024.075 rows=161,645 loops=1)

8. 1,119.275 2,896.667 ↑ 1.0 161,645 1

Sort (cost=32,865.85..33,287.16 rows=168,522 width=16) (actual time=2,845.569..2,896.667 rows=161,645 loops=1)

  • Sort Key: fcur.company_key, fcur.fcur_created_at
  • Sort Method: external merge Disk: 4752kB
9. 1,615.095 1,777.392 ↑ 1.0 161,645 1

Hash Join (cost=8,126.81..15,355.47 rows=168,522 width=16) (actual time=112.176..1,777.392 rows=161,645 loops=1)

  • Hash Cond: (fcur.user_key = du.user_key)
10. 51.145 51.145 ↑ 1.0 168,685 1

Seq Scan on fact_company_user_roles fcur (cost=0.00..4,514.85 rows=168,685 width=16) (actual time=0.095..51.145 rows=168,685 loops=1)

11. 51.412 111.152 ↑ 1.0 159,279 1

Hash (cost=5,512.55..5,512.55 rows=159,301 width=4) (actual time=111.152..111.152 rows=159,279 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2431kB
12. 59.740 59.740 ↑ 1.0 159,279 1

Seq Scan on dim_users du (cost=0.00..5,512.55 rows=159,301 width=4) (actual time=0.036..59.740 rows=159,279 loops=1)

  • Filter: (NOT is_agent)
  • Rows Removed by Filter: 176
13. 32,244.801 892,409.181 ↑ 2.4 3,504,748 1

Hash (cost=7,735,962.44..7,735,962.44 rows=8,428,446 width=480) (actual time=892,409.181..892,409.181 rows=3,504,748 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 4096 (originally 2048) Memory Usage: 644632kB
14. 4,974.256 860,164.380 ↑ 2.4 3,504,748 1

Merge Left Join (cost=7,538,845.15..7,735,962.44 rows=8,428,446 width=480) (actual time=764,439.311..860,164.380 rows=3,504,748 loops=1)

  • Merge 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: 4913717
15. 571,514.976 847,450.782 ↑ 1.0 8,418,465 1

Sort (cost=7,505,979.30..7,527,050.42 rows=8,428,446 width=476) (actual time=757,438.575..847,450.782 rows=8,418,465 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)
  • Sort Method: external merge Disk: 4025728kB
16. 275,935.806 275,935.806 ↑ 1.0 8,418,465 1

Seq Scan on fact_amplitude_events (cost=0.00..1,091,676.46 rows=8,428,446 width=476) (actual time=0.490..275,935.806 rows=8,418,465 loops=1)

17. 474.355 7,739.342 ↓ 2,380.0 2,006,323 1

Materialize (cost=32,865.85..38,344.93 rows=843 width=8) (actual time=7,000.723..7,739.342 rows=2,006,323 loops=1)

18. 68.341 7,264.987 ↓ 182.2 153,630 1

Subquery Scan on foo_1 (cost=32,865.85..38,342.82 rows=843 width=8) (actual time=7,000.719..7,264.987 rows=153,630 loops=1)

  • Filter: (foo_1.rk = 1)
  • Rows Removed by Filter: 8015
19. 141.977 7,196.646 ↑ 1.0 161,645 1

WindowAgg (cost=32,865.85..36,236.29 rows=168,522 width=24) (actual time=7,000.716..7,196.646 rows=161,645 loops=1)

20. 146.167 7,054.669 ↑ 1.0 161,645 1

Sort (cost=32,865.85..33,287.16 rows=168,522 width=16) (actual time=7,000.708..7,054.669 rows=161,645 loops=1)

  • Sort Key: fcur_1.company_key, fcur_1.fcur_created_at
  • Sort Method: external merge Disk: 4752kB
21. 3,027.735 6,908.502 ↑ 1.0 161,645 1

Hash Join (cost=8,126.81..15,355.47 rows=168,522 width=16) (actual time=6,125.158..6,908.502 rows=161,645 loops=1)

  • Hash Cond: (fcur_1.user_key = du_1.user_key)
22. 52.165 52.165 ↑ 1.0 168,685 1

Seq Scan on fact_company_user_roles fcur_1 (cost=0.00..4,514.85 rows=168,685 width=16) (actual time=0.102..52.165 rows=168,685 loops=1)

23. 3,770.438 3,828.602 ↑ 1.0 159,279 1

Hash (cost=5,512.55..5,512.55 rows=159,301 width=4) (actual time=3,828.602..3,828.602 rows=159,279 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2431kB
24. 58.164 58.164 ↑ 1.0 159,279 1

Seq Scan on dim_users du_1 (cost=0.00..5,512.55 rows=159,301 width=4) (actual time=0.031..58.164 rows=159,279 loops=1)

  • Filter: (NOT is_agent)
  • Rows Removed by Filter: 176
25. 47.312 95.225 ↑ 1.0 154,349 1

Hash (cost=9,927.49..9,927.49 rows=154,349 width=4) (actual time=95.225..95.225 rows=154,349 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3737kB
26. 47.913 47.913 ↑ 1.0 154,349 1

Seq Scan on dim_companies dc (cost=0.00..9,927.49 rows=154,349 width=4) (actual time=0.007..47.913 rows=154,349 loops=1)

Planning time : 2.856 ms
Execution time : 1,788,128.824 ms