explain.depesz.com

PostgreSQL's explain analyze made readable

Result: z8DW

Settings
# exclusive inclusive rows x rows loops node
1. 4,835.607 50,294.996 ↓ 8.1 12,342,509 1

WindowAgg (cost=1,158,547,268.18..1,158,577,744.58 rows=1,523,820 width=36) (actual time=26,598.005..50,294.996 rows=12,342,509 loops=1)

2.          

CTE churners

3. 0.002 5,046.392 ↑ 6,739.0 1 1

Subquery Scan on t (cost=561,817.61..561,952.40 rows=6,739 width=28) (actual time=5,046.392..5,046.392 rows=1 loops=1)

4. 3,757.359 5,046.390 ↑ 6,739.0 1 1

HashAggregate (cost=561,817.61..561,885.01 rows=6,739 width=28) (actual time=5,046.390..5,046.390 rows=1 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: 251
5. 1,289.031 1,289.031 ↑ 1.0 15,251,254 1

Seq Scan on global_attendances gal (cost=0.00..371,153.94 rows=15,253,094 width=28) (actual time=9.233..1,289.031 rows=15,251,254 loops=1)

6. 30,378.830 45,459.389 ↓ 8.1 12,342,509 1

Sort (cost=1,157,985,315.79..1,157,989,125.34 rows=1,523,820 width=28) (actual time=26,597.996..45,459.389 rows=12,342,509 loops=1)

  • Sort Key: global_attendances.cust_id, global_attendances.jour DESC
  • Sort Method: external merge Disk: 506,472kB
7. 2,748.975 15,080.559 ↓ 8.1 12,342,509 1

Group (cost=0.56..1,157,792,365.60 rows=1,523,820 width=28) (actual time=5,046.488..15,080.559 rows=12,342,509 loops=1)

  • Group Key: global_attendances.cust_id, global_attendances.jour
8. 12,331.584 12,331.584 ↓ 2.0 15,251,254 1

Index Only Scan using pk_summary_global_attendances on global_attendances (cost=0.56..1,157,754,232.87 rows=7,626,547 width=28) (actual time=5,046.485..12,331.584 rows=15,251,254 loops=1)

  • Filter: (SubPlan 2)
  • Heap Fetches: 30,685
9.          

SubPlan (for Index Only Scan)

10. 0.000 0.000 ↑ 6,739.0 1 15,251,254

CTE Scan on churners (cost=0.00..134.78 rows=6,739 width=68) (actual time=0.000..0.000 rows=1 loops=15,251,254)

Planning time : 0.313 ms
Execution time : 50,733.161 ms