explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4rb9

Settings
# exclusive inclusive rows x rows loops node
1. 0.210 98,021.216 ↑ 462,882.4 96 1

Sort (cost=292,034,554.28..292,145,646.06 rows=44,436,712 width=92) (actual time=98,021.208..98,021.216 rows=96 loops=1)

  • Sort Key: churners.jour DESC
  • Sort Method: quicksort Memory: 32kB
2.          

CTE churners

3. 0.014 6,264.678 ↑ 76.4 88 1

Subquery Scan on t (cost=515,880.69..516,015.23 rows=6,727 width=28) (actual time=6,260.998..6,264.678 rows=88 loops=1)

4. 3,947.426 6,264.664 ↑ 76.4 88 1

HashAggregate (cost=515,880.69..515,947.96 rows=6,727 width=28) (actual time=6,260.997..6,264.664 rows=88 loops=1)

  • Group Key: gal.cust_id
  • Filter: ((max(gal.jour) >= '2020-04-14 09:04:38.37+02'::timestamp with time zone) AND (max(gal.jour) <= '2020-10-14 09:04:38.37+02'::timestamp with time zone) AND (max(gal.jour) < (now() - '90 days'::interval)))
  • Rows Removed by Filter: 8,267
5. 2,317.238 2,317.238 ↓ 1.2 15,243,883 1

Seq Scan on global_attendances gal (cost=0.00..350,737.53 rows=13,211,453 width=28) (actual time=8.542..2,317.238 rows=15,243,883 loops=1)

6. 0.589 98,021.006 ↑ 462,882.4 96 1

Hash Right Join (cost=380.75..283,595,658.94 rows=44,436,712 width=92) (actual time=16,429.483..98,021.006 rows=96 loops=1)

  • Hash Cond: ((ga1.cust_id)::text = (churners.cust_id)::text)
7. 26,256.211 91,755.641 ↑ 52,845.8 25 1

GroupAggregate (cost=162.12..281,912,053.87 rows=1,321,145 width=36) (actual time=10,164.617..91,755.641 rows=25 loops=1)

  • Group Key: ga1.cust_id, ga1.jour
  • Filter: ((ga1.jour - max(ga2.jour)) > '30 days'::interval)
  • Rows Removed by Filter: 136,644
8. 18,239.138 65,499.430 ↑ 25.0 197,156,106 1

Nested Loop (cost=162.12..232,678,396.02 rows=4,922,044,640 width=36) (actual time=96.723..65,499.430 rows=197,156,106 loops=1)

9. 1,639.579 8,694.467 ↑ 40.6 162,725 1

Merge Join (cost=161.56..1,318,378.71 rows=6,605,726 width=96) (actual time=96.653..8,694.467 rows=162,725 loops=1)

  • Merge Cond: ((ga1.cust_id)::text = (churners_1.cust_id)::text)
10. 7,030.490 7,030.490 ↓ 1.2 15,237,564 1

Index Only Scan using pk_summary_global_attendances on global_attendances ga1 (cost=0.56..1,279,297.24 rows=13,211,453 width=28) (actual time=0.035..7,030.490 rows=15,237,564 loops=1)

  • Heap Fetches: 36,503
11. 24.338 24.398 ↓ 795.7 159,144 1

Sort (cost=161.00..161.50 rows=200 width=68) (actual time=0.219..24.398 rows=159,144 loops=1)

  • Sort Key: churners_1.cust_id
  • Sort Method: quicksort Memory: 31kB
12. 0.051 0.060 ↑ 2.3 88 1

HashAggregate (cost=151.36..153.36 rows=200 width=68) (actual time=0.048..0.060 rows=88 loops=1)

  • Group Key: (churners_1.cust_id)::text
13. 0.009 0.009 ↑ 76.4 88 1

CTE Scan on churners churners_1 (cost=0.00..134.54 rows=6,727 width=68) (actual time=0.003..0.009 rows=88 loops=1)

14. 38,565.825 38,565.825 ↓ 1.9 1,212 162,725

Index Only Scan using pk_summary_global_attendances on global_attendances ga2 (cost=0.56..28.47 rows=655 width=28) (actual time=0.012..0.237 rows=1,212 loops=162,725)

  • Index Cond: ((cust_id = (ga1.cust_id)::text) AND (jour < ga1.jour))
  • Heap Fetches: 0
15. 0.059 6,264.776 ↑ 76.4 88 1

Hash (cost=134.54..134.54 rows=6,727 width=76) (actual time=6,264.776..6,264.776 rows=88 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 70kB
16. 6,264.717 6,264.717 ↑ 76.4 88 1

CTE Scan on churners (cost=0.00..134.54 rows=6,727 width=76) (actual time=6,261.006..6,264.717 rows=88 loops=1)

Planning time : 2.160 ms
Execution time : 98,021.504 ms