explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3PfK

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 5,675.097 ↑ 7.5 90 1

Sort (cost=2,558,400.95..2,558,402.63 rows=674 width=124) (actual time=5,675.089..5,675.097 rows=90 loops=1)

  • Sort Key: c.jour DESC
  • Sort Method: quicksort Memory: 33kB
2.          

CTE churners

3. 0.011 5,043.515 ↑ 74.9 90 1

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

4. 3,896.966 5,043.504 ↑ 74.9 90 1

HashAggregate (cost=561,817.61..561,885.01 rows=6,739 width=28) (actual time=5,040.880..5,043.504 rows=90 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,269
5. 1,146.538 1,146.538 ↑ 1.0 15,251,270 1

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

6.          

CTE seasonal

7. 24.483 631.007 ↑ 20,337.4 25 1

Subquery Scan on t_1 (cost=1,368,844.47..1,423,501.37 rows=508,436 width=44) (actual time=536.993..631.007 rows=25 loops=1)

  • Filter: ((t_1.lg - t_1.jour) > '30 days'::interval)
  • Rows Removed by Filter: 140,149
8. 67.661 606.524 ↑ 10.9 140,174 1

WindowAgg (cost=1,368,844.47..1,399,350.65 rows=1,525,309 width=36) (actual time=527.764..606.524 rows=140,174 loops=1)

9. 160.360 538.863 ↑ 10.9 140,174 1

Sort (cost=1,368,844.47..1,372,657.74 rows=1,525,309 width=28) (actual time=527.754..538.863 rows=140,174 loops=1)

  • Sort Key: global_attendances.cust_id, global_attendances.jour DESC
  • Sort Method: quicksort Memory: 17,096kB
10. 31.140 378.503 ↑ 10.9 140,174 1

Group (cost=1,118,496.52..1,175,695.62 rows=1,525,309 width=28) (actual time=334.277..378.503 rows=140,174 loops=1)

  • Group Key: global_attendances.cust_id, global_attendances.jour
11. 293.320 347.363 ↑ 45.8 166,512 1

Sort (cost=1,118,496.52..1,137,562.89 rows=7,626,547 width=28) (actual time=334.274..347.363 rows=166,512 loops=1)

  • Sort Key: global_attendances.cust_id, global_attendances.jour
  • Sort Method: quicksort Memory: 19,153kB
12. 16.604 54.043 ↑ 45.8 166,512 1

Nested Loop (cost=152.19..64,210.61 rows=7,626,547 width=28) (actual time=0.130..54.043 rows=166,512 loops=1)

13. 0.068 0.089 ↑ 2.2 90 1

HashAggregate (cost=151.63..153.63 rows=200 width=68) (actual time=0.057..0.089 rows=90 loops=1)

  • Group Key: (churners.cust_id)::text
14. 0.021 0.021 ↑ 74.9 90 1

CTE Scan on churners (cost=0.00..134.78 rows=6,739 width=68) (actual time=0.002..0.021 rows=90 loops=1)

15. 37.350 37.350 ↑ 1.2 1,850 90

Index Only Scan using pk_summary_global_attendances on global_attendances (cost=0.56..297.65 rows=2,263 width=28) (actual time=0.019..0.415 rows=1,850 loops=90)

  • Index Cond: (cust_id = (churners.cust_id)::text)
  • Heap Fetches: 0
16. 0.128 5,675.053 ↑ 7.5 90 1

HashAggregate (cost=572,907.09..572,915.51 rows=674 width=124) (actual time=5,675.022..5,675.053 rows=90 loops=1)

  • Group Key: c.jour, c.cust_id
17. 0.069 5,674.925 ↑ 174,813.8 98 1

Merge Left Join (cost=58,920.86..315,930.82 rows=17,131,751 width=100) (actual time=5,674.851..5,674.925 rows=98 loops=1)

  • Merge Cond: ((c.cust_id)::text = (s.cust_id)::text)
18. 0.216 5,043.781 ↑ 74.9 90 1

Sort (cost=563.32..580.17 rows=6,739 width=76) (actual time=5,043.764..5,043.781 rows=90 loops=1)

  • Sort Key: c.cust_id
  • Sort Method: quicksort Memory: 32kB
19. 5,043.565 5,043.565 ↑ 74.9 90 1

CTE Scan on churners c (cost=0.00..134.78 rows=6,739 width=76) (actual time=5,040.891..5,043.565 rows=90 loops=1)

20. 0.044 631.075 ↑ 20,337.4 25 1

Sort (cost=58,357.54..59,628.63 rows=508,436 width=92) (actual time=631.072..631.075 rows=25 loops=1)

  • Sort Key: s.cust_id
  • Sort Method: quicksort Memory: 26kB
21. 631.031 631.031 ↑ 20,337.4 25 1

CTE Scan on seasonal s (cost=0.00..10,168.72 rows=508,436 width=92) (actual time=537.000..631.031 rows=25 loops=1)

Planning time : 0.881 ms
Execution time : 5,677.706 ms