explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fb6S

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 7,715.561 ↑ 1,037.5 16 1

Sort (cost=973,650.89..973,692.39 rows=16,600 width=68) (actual time=7,715.560..7,715.561 rows=16 loops=1)

  • Sort Key: (to_date(series.key, 'Mon YYYY'::text))
  • Sort Method: quicksort Memory: 29kB
2. 0.078 7,715.551 ↑ 1,037.5 16 1

GroupAggregate (cost=972,113.82..972,487.32 rows=16,600 width=68) (actual time=7,715.491..7,715.551 rows=16 loops=1)

  • Group Key: series.key
3. 0.053 7,715.473 ↑ 129.7 128 1

Sort (cost=972,113.82..972,155.32 rows=16,600 width=72) (actual time=7,715.466..7,715.473 rows=128 loops=1)

  • Sort Key: series.key
  • Sort Method: quicksort Memory: 35kB
4. 0.091 7,715.420 ↑ 129.7 128 1

Merge Left Join (cost=970,548.78..970,784.25 rows=16,600 width=72) (actual time=7,715.323..7,715.420 rows=128 loops=1)

  • Merge Cond: ((series.key = data.key) AND (user_types.ut = data.user_type))
5.          

CTE user_types

6. 184.867 3,213.513 ↑ 10.4 8 1

Unique (cost=483,678.20..483,717.45 rows=83 width=8) (actual time=2,823.945..3,213.513 rows=8 loops=1)

7. 1,042.022 3,028.646 ↓ 265.9 2,087,062 1

Sort (cost=483,678.20..483,697.83 rows=7,850 width=8) (actual time=2,823.942..3,028.646 rows=2,087,062 loops=1)

  • Sort Key: r.user_type
  • Sort Method: external merge Disk: 38632kB
8. 304.736 1,986.624 ↓ 265.9 2,087,062 1

Gather (cost=164,706.36..483,170.37 rows=7,850 width=8) (actual time=599.262..1,986.624 rows=2,087,062 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 411.365 1,681.888 ↓ 212.7 695,687 3 / 3

Parallel Hash Join (cost=163,706.36..481,385.37 rows=3,271 width=8) (actual time=592.428..1,681.888 rows=695,687 loops=3)

  • Hash Cond: (r.user_id = u.table_id)
10. 854.073 909.884 ↓ 11.7 707,866 3 / 3

Parallel Bitmap Heap Scan on usage_activity r (cost=3,864.27..321,303.60 rows=60,281 width=16) (actual time=228.979..909.884 rows=707,866 loops=3)

  • Recheck Cond: ((hub_id = 39) AND (cluster_id = 39))
  • Filter: (user_type IS NOT NULL)
  • Rows Removed by Filter: 4668
  • Heap Blocks: exact=57286
11. 55.811 55.811 ↓ 12.2 2,137,602 1 / 3

Bitmap Index Scan on usage_activity_hub_cluster_idx (cost=0.00..3,828.10 rows=175,554 width=0) (actual time=167.434..167.434 rows=2,137,602 loops=1)

  • Index Cond: ((hub_id = 39) AND (cluster_id = 39))
12. 95.708 360.639 ↑ 1.2 150,781 3 / 3

Parallel Hash (cost=157,607.80..157,607.80 rows=178,743 width=8) (actual time=360.639..360.639 rows=150,781 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 21856kB
13. 254.423 264.931 ↑ 1.2 150,781 3 / 3

Parallel Bitmap Heap Scan on users u (cost=8,652.53..157,607.80 rows=178,743 width=8) (actual time=38.632..264.931 rows=150,781 loops=3)

  • Recheck Cond: (hub_id = 39)
  • Filter: (((user_type IS NOT NULL) AND (signup_stage IS NULL) AND (import_status = 'joined'::text)) OR ((signup_stage IS NOT NULL) AND (user_type IS NOT NULL) AND (import_status = 'joined'::text)) OR (import_status = 'imported'::text))
  • Rows Removed by Filter: 67
  • Heap Blocks: exact=17541
14. 10.508 10.508 ↑ 1.0 452,542 1 / 3

Bitmap Index Scan on users_hub_cluster_idx (cost=0.00..8,545.28 rows=462,513 width=0) (actual time=31.524..31.524 rows=452,542 loops=1)

  • Index Cond: (hub_id = 39)
15.          

CTE data

16. 549.287 4,500.675 ↑ 80.1 119 1

GroupAggregate (cost=484,046.25..484,355.85 rows=9,526 width=56) (actual time=3,669.596..4,500.675 rows=119 loops=1)

  • Group Key: (date_trunc('quarter'::text, (r_1.created_at)::timestamp with time zone)), r_1.user_type
17. 1,527.563 3,951.388 ↓ 219.3 2,089,457 1

Sort (cost=484,046.25..484,070.07 rows=9,526 width=24) (actual time=3,669.554..3,951.388 rows=2,089,457 loops=1)

  • Sort Key: (date_trunc('quarter'::text, (r_1.created_at)::timestamp with time zone)), r_1.user_type
  • Sort Method: external merge Disk: 72320kB
18. 498.366 2,423.825 ↓ 219.3 2,089,457 1

Gather (cost=164,714.08..483,416.69 rows=9,526 width=24) (actual time=594.487..2,423.825 rows=2,089,457 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 834.872 1,925.459 ↓ 175.5 696,486 3 / 3

Parallel Hash Join (cost=163,714.08..481,464.09 rows=3,969 width=24) (actual time=590.824..1,925.459 rows=696,486 loops=3)

  • Hash Cond: (r_1.user_id = u_1.table_id)
20. 678.615 733.984 ↓ 9.7 712,534 3 / 3

Parallel Bitmap Heap Scan on usage_activity r_1 (cost=3,871.99..321,311.32 rows=73,148 width=20) (actual time=232.292..733.984 rows=712,534 loops=3)

  • Recheck Cond: ((hub_id = 39) AND (cluster_id = 39))
  • Heap Blocks: exact=48365
21. 55.369 55.369 ↓ 12.2 2,137,602 1 / 3

Bitmap Index Scan on usage_activity_hub_cluster_idx (cost=0.00..3,828.10 rows=175,554 width=0) (actual time=166.106..166.106 rows=2,137,602 loops=1)

  • Index Cond: ((hub_id = 39) AND (cluster_id = 39))
22. 92.446 356.603 ↑ 1.2 150,781 3 / 3

Parallel Hash (cost=157,607.80..157,607.80 rows=178,743 width=8) (actual time=356.602..356.603 rows=150,781 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 21824kB
23. 254.193 264.157 ↑ 1.2 150,781 3 / 3

Parallel Bitmap Heap Scan on users u_1 (cost=8,652.53..157,607.80 rows=178,743 width=8) (actual time=37.790..264.157 rows=150,781 loops=3)

  • Recheck Cond: (hub_id = 39)
  • Filter: (((user_type IS NOT NULL) AND (signup_stage IS NULL) AND (import_status = 'joined'::text)) OR ((signup_stage IS NOT NULL) AND (user_type IS NOT NULL) AND (import_status = 'joined'::text)) OR (import_status = 'imported'::text))
  • Rows Removed by Filter: 67
  • Heap Blocks: exact=14472
24. 9.964 9.964 ↑ 1.0 452,542 1 / 3

Bitmap Index Scan on users_hub_cluster_idx (cost=0.00..8,545.28 rows=462,513 width=0) (actual time=29.891..29.891 rows=452,542 loops=1)

  • Index Cond: (hub_id = 39)
25.          

CTE series

26. 0.016 4,501.275 ↑ 62.5 16 1

Subquery Scan on t (cost=238.15..255.67 rows=1,000 width=32) (actual time=4,501.256..4,501.275 rows=16 loops=1)

27. 0.016 4,501.259 ↑ 62.5 16 1

ProjectSet (cost=238.15..243.17 rows=1,000 width=8) (actual time=4,501.251..4,501.259 rows=16 loops=1)

28. 0.336 4,501.243 ↑ 1.0 1 1

Aggregate (cost=238.15..238.16 rows=1 width=4) (actual time=4,501.243..4,501.243 rows=1 loops=1)

29. 4,500.907 4,500.907 ↑ 80.1 119 1

CTE Scan on data data_1 (cost=0.00..190.52 rows=9,526 width=32) (actual time=3,669.600..4,500.907 rows=119 loops=1)

30. 0.254 7,715.141 ↑ 129.7 128 1

Sort (cost=1,399.73..1,441.23 rows=16,600 width=64) (actual time=7,715.133..7,715.141 rows=128 loops=1)

  • Sort Key: series.key, user_types.ut
  • Sort Method: quicksort Memory: 34kB
31. 0.038 7,714.887 ↑ 129.7 128 1

Nested Loop (cost=22.50..236.16 rows=16,600 width=64) (actual time=7,325.252..7,714.887 rows=128 loops=1)

32. 3,213.529 3,213.529 ↑ 10.4 8 1

CTE Scan on user_types (cost=0.00..1.66 rows=83 width=32) (actual time=2,823.947..3,213.529 rows=8 loops=1)

33. 0.019 4,501.320 ↑ 12.5 16 8

Materialize (cost=22.50..27.50 rows=200 width=32) (actual time=562.663..562.665 rows=16 loops=8)

34. 0.017 4,501.301 ↑ 12.5 16 1

HashAggregate (cost=22.50..24.50 rows=200 width=32) (actual time=4,501.297..4,501.301 rows=16 loops=1)

  • Group Key: series.key
35. 4,501.284 4,501.284 ↑ 62.5 16 1

CTE Scan on series (cost=0.00..20.00 rows=1,000 width=32) (actual time=4,501.260..4,501.284 rows=16 loops=1)

36. 0.150 0.188 ↑ 80.1 119 1

Sort (cost=820.08..843.89 rows=9,526 width=72) (actual time=0.180..0.188 rows=119 loops=1)

  • Sort Key: data.key, data.user_type
  • Sort Method: quicksort Memory: 34kB
37. 0.038 0.038 ↑ 80.1 119 1

CTE Scan on data (cost=0.00..190.52 rows=9,526 width=72) (actual time=0.003..0.038 rows=119 loops=1)

Planning time : 0.737 ms
Execution time : 7,762.277 ms