explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QUjZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.753 36,932.988 ↓ 37.2 1,377 1

Sort (cost=114,853.12..114,853.22 rows=37 width=112) (actual time=36,932.891..36,932.988 rows=1,377 loops=1)

  • Sort Key: zz.z___pivot_row_rank
  • Sort Method: quicksort Memory: 242kB
2.          

CTE product_dnu

3. 10.087 36,892.305 ↑ 1.0 9,612 1

HashAggregate (cost=114,109.05..114,207.82 rows=9,877 width=44) (actual time=36,889.160..36,892.305 rows=9,612 loops=1)

  • Group Key: (' Actual'::text), dim_user.dim_date_install, (count(*))
4. 1.520 36,882.218 ↑ 1.0 9,612 1

Append (cost=8.06..114,034.97 rows=9,877 width=44) (actual time=13.105..36,882.218 rows=9,612 loops=1)

5. 0.001 1.136 ↓ 0.0 0 1

GroupAggregate (cost=8.06..8.08 rows=1 width=44) (actual time=1.136..1.136 rows=0 loops=1)

  • Group Key: dim_user.dim_date_install
6. 0.008 1.135 ↓ 0.0 0 1

Sort (cost=8.06..8.06 rows=1 width=4) (actual time=1.135..1.135 rows=0 loops=1)

  • Sort Key: dim_user.dim_date_install
  • Sort Method: quicksort Memory: 25kB
7. 1.127 1.127 ↓ 0.0 0 1

Index Scan using dim_user_product_id_user_id_key on dim_user (cost=0.42..8.05 rows=1 width=4) (actual time=1.127..1.127 rows=0 loops=1)

  • Index Cond: ((product_id)::text = '00u32ewyu5dZCMS7n357'::text)
8. 4.940 12.171 ↑ 1.0 1,412 1

HashAggregate (cost=2,096.65..2,110.91 rows=1,426 width=44) (actual time=11.968..12.171 rows=1,412 loops=1)

  • Group Key: fact_aggregated_activity.dim_date_activity
9. 7.231 7.231 ↑ 1.0 31,064 1

Seq Scan on fact_aggregated_activity (cost=0.00..1,940.86 rows=31,157 width=8) (actual time=0.007..7.231 rows=31,064 loops=1)

  • Filter: ((product_id)::text = '00u32ewyu5dZCMS7n357'::text)
  • Rows Removed by Filter: 50328
10. 3.696 36,867.391 ↑ 1.0 8,200 1

Subquery Scan on *SELECT* 3 (cost=108,423.74..111,901.72 rows=8,450 width=44) (actual time=30,274.956..36,867.391 rows=8,200 loops=1)

11. 166.248 36,863.695 ↑ 1.0 8,200 1

Finalize GroupAggregate (cost=108,423.74..111,817.22 rows=8,450 width=1,136) (actual time=30,274.955..36,863.695 rows=8,200 loops=1)

  • Group Key: scenario.config, fnu.dim_date_install
12. 2,222.484 36,697.447 ↓ 1.5 24,600 1

Gather Merge (cost=108,423.74..111,394.72 rows=16,900 width=1,104) (actual time=30,274.128..36,697.447 rows=24,600 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 1,375.707 34,474.963 ↑ 1.0 8,200 3 / 3

Partial GroupAggregate (cost=107,423.71..108,444.01 rows=8,450 width=1,104) (actual time=29,875.819..34,474.963 rows=8,200 loops=3)

  • Group Key: scenario.config, fnu.dim_date_install
14. 32,843.883 33,099.256 ↓ 2.0 182,587 3 / 3

Sort (cost=107,423.71..107,657.66 rows=93,580 width=1,104) (actual time=29,875.391..33,099.256 rows=182,587 loops=3)

  • Sort Key: scenario.config, fnu.dim_date_install
  • Sort Method: external merge Disk: 200912kB
15. 73.449 255.373 ↓ 2.0 182,587 3 / 3

Hash Left Join (cost=36.71..9,494.85 rows=93,580 width=1,104) (actual time=18.420..255.373 rows=182,587 loops=3)

  • Hash Cond: ((fnu.scenario_id = fnuo.scenario_id) AND (fnu.dim_date_install = fnuo.dim_date_install))
  • Join Filter: ((((scenario.config -> 'newUsers'::text) -> 'overrideModel'::text))::character varying)::boolean
16. 120.335 181.678 ↓ 2.0 182,587 3 / 3

Hash Join (cost=15.91..8,982.76 rows=93,580 width=1,104) (actual time=18.158..181.678 rows=182,587 loops=3)

  • Hash Cond: (fnu.scenario_id = scenario.id)
17. 61.302 61.302 ↑ 1.2 239,565 3 / 3

Parallel Seq Scan on fact_new_users fnu (cost=0.00..7,972.56 rows=299,456 width=12) (actual time=2.825..61.302 rows=239,565 loops=3)

18. 0.006 0.041 ↑ 1.0 5 3 / 3

Hash (cost=15.85..15.85 rows=5 width=1,096) (actual time=0.041..0.041 rows=5 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
19. 0.014 0.035 ↑ 1.0 5 3 / 3

Bitmap Heap Scan on scenario (cost=4.17..15.85 rows=5 width=1,096) (actual time=0.029..0.035 rows=5 loops=3)

  • Recheck Cond: ((product_id)::text = '00u32ewyu5dZCMS7n357'::text)
  • Heap Blocks: exact=10
20. 0.021 0.021 ↓ 2.2 11 3 / 3

Bitmap Index Scan on scenario_product_id_idx (cost=0.00..4.17 rows=5 width=0) (actual time=0.021..0.021 rows=11 loops=3)

  • Index Cond: ((product_id)::text = '00u32ewyu5dZCMS7n357'::text)
21. 0.138 0.246 ↓ 1.1 626 3 / 3

Hash (cost=11.92..11.92 rows=592 width=12) (actual time=0.246..0.246 rows=626 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
22. 0.108 0.108 ↓ 1.1 626 3 / 3

Seq Scan on fact_new_users_override fnuo (cost=0.00..11.92 rows=592 width=12) (actual time=0.008..0.108 rows=626 loops=3)

23. 0.217 36,932.235 ↓ 37.2 1,377 1

Subquery Scan on zz (cost=630.36..644.34 rows=37 width=112) (actual time=36,930.908..36,932.235 rows=1,377 loops=1)

  • Filter: (((zz.z__pivot_col_rank <= 50) OR (zz.z__is_highest_ranked_cell = 1)) AND ((zz.z___pivot_row_rank <= 5000) OR (zz.z__pivot_col_ordering = 1)))
24. 1.018 36,932.018 ↓ 4.2 1,377 1

WindowAgg (cost=630.36..637.76 rows=329 width=112) (actual time=36,930.906..36,932.018 rows=1,377 loops=1)

25. 1.043 36,931.000 ↓ 4.2 1,377 1

Sort (cost=630.36..631.18 rows=329 width=100) (actual time=36,930.901..36,931.000 rows=1,377 loops=1)

  • Sort Key: xx.z__pivot_col_rank, xx.z___min_rank
  • Sort Method: quicksort Memory: 192kB
26. 0.929 36,929.957 ↓ 4.2 1,377 1

WindowAgg (cost=610.85..616.60 rows=329 width=100) (actual time=36,928.938..36,929.957 rows=1,377 loops=1)

27. 0.515 36,929.028 ↓ 4.2 1,377 1

Sort (cost=610.85..611.67 rows=329 width=92) (actual time=36,928.932..36,929.028 rows=1,377 loops=1)

  • Sort Key: xx.z___min_rank
  • Sort Method: quicksort Memory: 158kB
28. 0.220 36,928.513 ↓ 4.2 1,377 1

Subquery Scan on xx (cost=579.00..597.09 rows=329 width=92) (actual time=36,926.073..36,928.513 rows=1,377 loops=1)

29. 1.010 36,928.293 ↓ 4.2 1,377 1

WindowAgg (cost=579.00..593.80 rows=329 width=92) (actual time=36,926.072..36,928.293 rows=1,377 loops=1)

30. 0.194 36,927.283 ↓ 4.2 1,377 1

Subquery Scan on aa (cost=579.00..588.87 rows=329 width=84) (actual time=36,926.055..36,927.283 rows=1,377 loops=1)

31. 0.937 36,927.089 ↓ 4.2 1,377 1

WindowAgg (cost=579.00..585.58 rows=329 width=84) (actual time=36,926.054..36,927.089 rows=1,377 loops=1)

32. 0.809 36,926.152 ↓ 4.2 1,377 1

Sort (cost=579.00..579.82 rows=329 width=76) (actual time=36,926.048..36,926.152 rows=1,377 loops=1)

  • Sort Key: bb."product_dnu.granular_activity", bb.z__pivot_col_rank
  • Sort Method: quicksort Memory: 156kB
33. 0.219 36,925.343 ↓ 4.2 1,377 1

Subquery Scan on bb (cost=533.13..565.24 rows=329 width=76) (actual time=36,924.056..36,925.343 rows=1,377 loops=1)

  • Filter: (bb.z__pivot_col_rank <= 16384)
34. 0.970 36,925.124 ↓ 1.4 1,377 1

WindowAgg (cost=533.13..552.89 rows=988 width=80) (actual time=36,924.055..36,925.124 rows=1,377 loops=1)

35. 0.857 36,924.154 ↓ 1.4 1,377 1

Sort (cost=533.13..535.60 rows=988 width=72) (actual time=36,924.048..36,924.154 rows=1,377 loops=1)

  • Sort Key: (CASE WHEN (ww."product_dnu.scenario_name" IS NULL) THEN 1 ELSE 0 END), ww."product_dnu.scenario_name
  • Sort Method: quicksort Memory: 150kB
36. 0.199 36,923.297 ↓ 1.4 1,377 1

Subquery Scan on ww (cost=444.47..483.99 rows=988 width=72) (actual time=36,922.732..36,923.297 rows=1,377 loops=1)

37. 4.395 36,923.098 ↓ 1.4 1,377 1

HashAggregate (cost=444.47..474.11 rows=988 width=68) (actual time=36,922.731..36,923.098 rows=1,377 loops=1)

  • Group Key: product_dnu.scenario_name, to_date(to_char(date_trunc('week'::text, (to_date(((product_dnu.date_activity)::character varying)::text, 'YYYYMMDD'::text))::timestamp with time zone), 'YYYY-MM-DD'::text), 'YYYY-MM-DD'::text)
38. 36,918.703 36,918.703 ↑ 1.0 9,612 1

CTE Scan on product_dnu (cost=0.00..370.39 rows=9,877 width=44) (actual time=36,889.183..36,918.703 rows=9,612 loops=1)

Planning time : 0.942 ms
Execution time : 36,968.615 ms