explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f3hp

Settings
# exclusive inclusive rows x rows loops node
1. 12,411.060 188,754.762 ↑ 145.4 119 1

HashAggregate (cost=2,700,434,655.33..2,700,435,779.70 rows=17,298 width=482) (actual time=188,754.254..188,754.762 rows=119 loops=1)

  • Group Key: a.date_actual, b.version
2.          

CTE daily_logins

3. 1,057.405 31,823.110 ↑ 4.0 1,980,110 1

Unique (cost=2,574,806,704.12..2,691,868,175.42 rows=8,000,000 width=44) (actual time=30,324.366..31,823.110 rows=1,980,110 loops=1)

4. 6,258.365 30,765.705 ↑ 3,052.5 3,834,964 1

Sort (cost=2,574,806,704.12..2,604,072,071.94 rows=11,706,147,130 width=44) (actual time=30,324.365..30,765.705 rows=3,834,964 loops=1)

  • Sort Key: a_1.epic_id, (date(a_1.event_login)), b_1.first_login_time
  • Sort Method: quicksort Memory: 397,911kB
5. 1,997.659 24,507.340 ↑ 3,052.5 3,834,964 1

Merge Join (cost=750,043.67..205,611,451.72 rows=11,706,147,130 width=44) (actual time=21,586.518..24,507.340 rows=3,834,964 loops=1)

  • Merge Cond: (b_1.epic_id = a_1.epic_id)
6. 509.054 657.681 ↓ 1.2 940,863 1

Sort (cost=94,216.36..96,133.00 rows=766,656 width=40) (actual time=577.723..657.681 rows=940,863 loops=1)

  • Sort Key: b_1.epic_id
  • Sort Method: quicksort Memory: 98,081kB
7. 148.627 148.627 ↓ 1.2 940,863 1

Seq Scan on first_active b_1 (cost=0.00..19,282.56 rows=766,656 width=40) (actual time=0.013..148.627 rows=940,863 loops=1)

8. 18,767.693 21,852.000 ↓ 1.4 4,143,927 1

Sort (cost=655,827.31..663,461.86 rows=3,053,820 width=40) (actual time=21,008.779..21,852.000 rows=4,143,927 loops=1)

  • Sort Key: a_1.epic_id
  • Sort Method: quicksort Memory: 422,049kB
9. 3,084.307 3,084.307 ↓ 1.4 4,143,928 1

Seq Scan on logins_cleaned a_1 (cost=0.00..326,897.55 rows=3,053,820 width=40) (actual time=0.570..3,084.307 rows=4,143,928 loops=1)

  • Filter: (event_login > (now() - '150 days'::interval))
  • Rows Removed by Filter: 4,062,045
10.          

CTE prep

11. 69,889.736 153,235.763 ↓ 946.4 19,684,671 1

HashAggregate (cost=8,562,985.30..8,563,193.30 rows=20,800 width=64) (actual time=141,337.141..153,235.763 rows=19,684,671 loops=1)

  • Group Key: c.date_actual, a_2.epic_id
12. 9,242.852 83,346.027 ↓ 1.2 52,339,158 1

Nested Loop (cost=0.30..4,994,095.94 rows=43,259,265 width=48) (actual time=30,325.676..83,346.027 rows=52,339,158 loops=1)

13. 32,520.865 32,520.865 ↑ 1.3 1,980,110 1

CTE Scan on daily_logins a_2 (cost=0.00..180,000.00 rows=2,666,667 width=44) (actual time=30,324.371..32,520.865 rows=1,980,110 loops=1)

  • Filter: (date >= '2019-06-01'::date)
14. 41,582.310 41,582.310 ↓ 1.6 26 1,980,110

Index Only Scan using d_date_date_actual_idx on d_date c (cost=0.30..1.65 rows=16 width=4) (actual time=0.005..0.021 rows=26 loops=1,980,110)

  • Index Cond: ((date_actual >= a_2.date) AND (date_actual >= (now() - '150 days'::interval)) AND (date_actual < date(now())))
  • Filter: (a_2.date >= (date_actual - 27))
  • Rows Removed by Filter: 59
  • Heap Fetches: 168,952,165
15. 5,616.788 176,343.702 ↓ 957.6 16,563,706 1

Hash Left Join (cost=15.23..1,210.85 rows=17,298 width=150) (actual time=141,337.836..176,343.702 rows=16,563,706 loops=1)

  • Hash Cond: (a.date_actual = b.date_actual)
16. 170,726.238 170,726.238 ↓ 2,370.0 16,431,486 1

CTE Scan on prep a (cost=0.00..572.00 rows=6,933 width=32) (actual time=141,337.144..170,726.238 rows=16,431,486 loops=1)

  • Filter: (date_actual >= (now() - '120 days'::interval))
  • Rows Removed by Filter: 3,253,185
17. 0.062 0.676 ↑ 1.0 499 1

Hash (cost=8.99..8.99 rows=499 width=122) (actual time=0.676..0.676 rows=499 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
18. 0.614 0.614 ↑ 1.0 499 1

Seq Scan on releases b (cost=0.00..8.99 rows=499 width=122) (actual time=0.450..0.614 rows=499 loops=1)

Planning time : 0.602 ms
Execution time : 189,413.288 ms