explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q6Fv

Settings
# exclusive inclusive rows x rows loops node
1. 1,329.353 98,223.694 ↑ 11.1 1,938,841 1

Subquery Scan on a (cost=1,022,946.93..5,103,841.09 rows=21,540,353 width=88) (actual time=70,628.023..98,223.694 rows=1,938,841 loops=1)

  • Buffers: shared hit=24,980, temp read=211,734 written=211,771
  • Functions: 158
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 25.346 ms, Inlining 403.770 ms, Optimization 2033.418 ms, Emission 1489.193 ms, Total 3951.728 ms
2. 3,231.672 96,894.341 ↑ 11.1 1,938,841 1

Finalize GroupAggregate (cost=1,022,946.93..4,673,034.03 rows=21,540,353 width=80) (actual time=70,628.014..96,894.341 rows=1,938,841 loops=1)

  • Group Key: (time_bucket('1 day'::interval, timezone('Europe/Paris'::text, profilelibrary_mat.start_datetime_utc))), fu_contract_mat.sub_profile, fu_contract_mat.supply_duration, fu_contract_mat.subscribed_power, fu_contract_mat.entity, fu_contract_mat.offer, fu_contract_mat.sales_campaign
  • Buffers: shared hit=24,980, temp read=211,734 written=211,771
3. 17,065.831 93,662.669 ↑ 1.8 12,119,660 1

Gather Merge (cost=1,022,946.93..3,865,270.77 rows=21,540,354 width=80) (actual time=70,627.990..93,662.669 rows=12,119,660 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
  • Buffers: shared hit=181,067, temp read=1,426,845 written=1,427,098
4. 4,857.166 76,596.838 ↑ 2.1 1,731,380 7 / 7

Partial GroupAggregate (cost=1,021,946.83..1,246,325.52 rows=3,590,059 width=80) (actual time=67,905.347..76,596.838 rows=1,731,380 loops=7)

  • Group Key: (time_bucket('1 day'::interval, timezone('Europe/Paris'::text, profilelibrary_mat.start_datetime_utc))), fu_contract_mat.sub_profile, fu_contract_mat.supply_duration, fu_contract_mat.subscribed_power, fu_contract_mat.entity, fu_contract_mat.offer, fu_contract_mat.sales_campaign
  • Buffers: shared hit=181,067, temp read=1,426,845 written=1,427,098
5. 58,732.755 71,739.672 ↓ 3.7 13,294,643 7 / 7

Sort (cost=1,021,946.83..1,030,921.98 rows=3,590,059 width=104) (actual time=67,905.306..71,739.672 rows=13,294,643 loops=7)

  • Sort Key: (time_bucket('1 day'::interval, timezone('Europe/Paris'::text, profilelibrary_mat.start_datetime_utc))), fu_contract_mat.sub_profile, fu_contract_mat.supply_duration, fu_contract_mat.subscribed_power, fu_contract_mat.entity, fu_contract_mat.offer, fu_contract_mat.sales_campaign
  • Sort Method: external merge Disk: 1,631,536kB
  • Worker 0: Sort Method: external merge Disk: 1,597,352kB
  • Worker 1: Sort Method: external merge Disk: 1,520,696kB
  • Worker 2: Sort Method: external merge Disk: 1,552,304kB
  • Worker 3: Sort Method: external merge Disk: 1,602,080kB
  • Worker 4: Sort Method: external merge Disk: 1,515,488kB
  • Worker 5: Sort Method: external merge Disk: 1,546,952kB
  • Buffers: shared hit=181,067, temp read=1,426,845 written=1,427,098
6. 8,714.539 13,006.917 ↓ 3.7 13,294,643 7 / 7

Merge Join (cost=433,009.93..516,074.07 rows=3,590,059 width=104) (actual time=3,348.768..13,006.917 rows=13,294,643 loops=7)

  • Merge Cond: ((profilelibrary_mat.month = fu_contract_mat.month_delivery) AND (profilelibrary_mat.sub_profile = (fu_contract_mat.sub_profile)::text))
  • Buffers: shared hit=180,953, temp read=56,044 written=56,057
7. 2,391.788 3,436.104 ↑ 1.1 1,300,005 7 / 7

Sort (cost=415,553.84..419,252.84 rows=1,479,600 width=42) (actual time=3,214.658..3,436.104 rows=1,300,005 loops=7)

  • Sort Key: profilelibrary_mat.month, profilelibrary_mat.sub_profile
  • Sort Method: external merge Disk: 62,336kB
  • Worker 0: Sort Method: external merge Disk: 66,648kB
  • Worker 1: Sort Method: external merge Disk: 63,048kB
  • Worker 2: Sort Method: external merge Disk: 62,344kB
  • Worker 3: Sort Method: external merge Disk: 66,344kB
  • Worker 4: Sort Method: external merge Disk: 63,096kB
  • Worker 5: Sort Method: external merge Disk: 64,536kB
  • Buffers: shared hit=164,286, temp read=56,044 written=56,057
8. 1,044.316 1,044.316 ↑ 1.1 1,300,005 7 / 7

Parallel Index Scan using ix_profilelibrary_mat_start_datetime on profilelibrary_mat (cost=0.56..237,258.44 rows=1,479,600 width=42) (actual time=561.536..1,044.316 rows=1,300,005 loops=7)

  • Index Cond: ((start_datetime >= '2020-04-01 00:00:00'::timestamp without time zone) AND (start_datetime <= '2023-05-31 23:30:00'::timestamp without time zone))
  • Buffers: shared hit=164,286
9. 828.931 856.274 ↓ 130.2 13,296,949 7 / 7

Sort (cost=11,890.13..12,145.40 rows=102,106 width=88) (actual time=79.026..856.274 rows=13,296,949 loops=7)

  • Sort Key: fu_contract_mat.month_delivery, fu_contract_mat.sub_profile
  • Sort Method: quicksort Memory: 17,431kB
  • Worker 0: Sort Method: quicksort Memory: 17,431kB
  • Worker 1: Sort Method: quicksort Memory: 17,431kB
  • Worker 2: Sort Method: quicksort Memory: 17,431kB
  • Worker 3: Sort Method: quicksort Memory: 17,431kB
  • Worker 4: Sort Method: quicksort Memory: 17,431kB
  • Worker 5: Sort Method: quicksort Memory: 17,431kB
  • Buffers: shared hit=16,667
10. 27.343 27.343 ↑ 1.0 102,106 7 / 7

Seq Scan on fu_contract_mat (cost=0.00..3,395.06 rows=102,106 width=88) (actual time=0.031..27.343 rows=102,106 loops=7)

  • Buffers: shared hit=16,618
Execution time : 98,907.209 ms