explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u6HJ

Settings
# exclusive inclusive rows x rows loops node
1. 10.056 372,387.915 ↑ 1.2 16,792 1

Sort (cost=21,258,882.91..21,258,931.95 rows=19,617 width=1,128) (actual time=372,386.798..372,387.915 rows=16,792 loops=1)

  • Sort Key: (COALESCE(t_energy.te_source_file, t_quality.tq_source_file)), (COALESCE(t_energy.te_meter_id, t_quality.tq_meter_id)), (COALESCE(t_energy.te_frame_date, t_quality.tq_frame_date))
  • Sort Method: quicksort Memory: 7682kB
2.          

CTE t_quality

3. 1.495 176,655.522 ↑ 1.5 12,968 1

Subquery Scan on x (cost=10,624,486.03..10,626,651.92 rows=19,252 width=283) (actual time=176,646.216..176,655.522 rows=12,968 loops=1)

  • Filter: (x.r < 2)
  • Rows Removed by Filter: 6
4. 6.990 176,654.027 ↑ 4.5 12,974 1

WindowAgg (cost=10,624,486.03..10,625,929.96 rows=57,757 width=287) (actual time=176,646.214..176,654.027 rows=12,974 loops=1)

5. 31.353 176,647.037 ↑ 4.5 12,974 1

Sort (cost=10,624,486.03..10,624,630.43 rows=57,757 width=279) (actual time=176,646.197..176,647.037 rows=12,974 loops=1)

  • Sort Key: t.meter_id, t.frame_date, t.num_ligne DESC
  • Sort Method: quicksort Memory: 3831kB
6. 9.584 176,615.684 ↑ 4.5 12,974 1

Gather (cost=1,000.00..10,619,918.12 rows=57,757 width=279) (actual time=176,597.111..176,615.684 rows=12,974 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 0.462 176,606.100 ↑ 5.6 4,325 3

Result (cost=0.00..10,613,383.08 rows=24,066 width=279) (actual time=176,589.197..176,606.100 rows=4,325 loops=3)

8. 0.192 176,605.638 ↑ 5.6 4,325 3

Append (cost=0.00..10,613,142.42 rows=24,066 width=279) (actual time=176,589.196..176,605.638 rows=4,325 loops=3)

9. 0.009 0.009 ↓ 0.0 0 3

Parallel Seq Scan on kaifa_load_profiles_before_201901 t (cost=0.00..10.71 rows=1 width=492) (actual time=0.008..0.009 rows=0 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
  • Rows Removed by Filter: 1
10. 15,965.682 15,965.682 ↓ 0.0 0 3

Parallel Seq Scan on kaifa_load_profiles_201901 t_1 (cost=0.00..1,177,257.94 rows=3,563 width=279) (actual time=15,965.682..15,965.682 rows=0 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
  • Rows Removed by Filter: 13852581
11. 32,195.620 32,195.620 ↓ 0.0 0 3

Parallel Seq Scan on kaifa_load_profiles_201902 t_2 (cost=0.00..2,055,905.24 rows=3,869 width=278) (actual time=32,195.620..32,195.620 rows=0 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
  • Rows Removed by Filter: 25007875
12. 42,771.474 42,771.474 ↓ 0.0 0 3

Parallel Seq Scan on kaifa_load_profiles_201903 t_3 (cost=0.00..2,922,415.23 rows=5,180 width=279) (actual time=42,771.474..42,771.474 rows=0 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
  • Rows Removed by Filter: 35776400
13. 64,454.754 64,454.754 ↓ 0.0 0 3

Parallel Seq Scan on kaifa_load_profiles_201904 t_4 (cost=0.00..3,143,047.88 rows=5,749 width=279) (actual time=64,454.754..64,454.754 rows=0 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
  • Rows Removed by Filter: 38475871
14. 21,217.907 21,217.907 ↑ 1.3 4,325 3

Parallel Seq Scan on kaifa_load_profiles_201905 t_5 (cost=0.00..1,314,494.71 rows=5,703 width=279) (actual time=21,201.653..21,217.907 rows=4,325 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
  • Rows Removed by Filter: 16085974
15. 0.000 0.000 ↓ 0.0 0 3

Parallel Seq Scan on kaifa_load_profiles_201906 t_6 (cost=0.00..10.71 rows=1 width=492) (actual time=0.000..0.000 rows=0 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
16.          

CTE t_energy

17. 1.474 195,689.724 ↑ 1.4 13,637 1

Subquery Scan on x_1 (cost=10,624,690.11..10,626,897.06 rows=19,617 width=382) (actual time=195,680.613..195,689.724 rows=13,637 loops=1)

  • Filter: (x_1.r < 2)
  • Rows Removed by Filter: 7
18. 7.023 195,688.250 ↑ 4.3 13,644 1

WindowAgg (cost=10,624,690.11..10,626,161.41 rows=58,852 width=386) (actual time=195,680.612..195,688.250 rows=13,644 loops=1)

19. 27.963 195,681.227 ↑ 4.3 13,644 1

Sort (cost=10,624,690.11..10,624,837.24 rows=58,852 width=378) (actual time=195,680.604..195,681.227 rows=13,644 loops=1)

  • Sort Key: t_7.meter_id, t_7.frame_date, t_7.num_ligne DESC
  • Sort Method: quicksort Memory: 2303kB
20. 5.224 195,653.264 ↑ 4.3 13,644 1

Gather (cost=1,000.00..10,620,027.62 rows=58,852 width=378) (actual time=195,635.008..195,653.264 rows=13,644 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
21. 0.458 195,648.040 ↑ 5.4 4,548 3

Result (cost=0.00..10,613,387.64 rows=24,522 width=378) (actual time=195,631.660..195,648.040 rows=4,548 loops=3)

22. 0.196 195,647.582 ↑ 5.4 4,548 3

Append (cost=0.00..10,613,142.42 rows=24,522 width=378) (actual time=195,631.658..195,647.582 rows=4,548 loops=3)

23. 0.003 0.003 ↓ 0.0 0 3

Parallel Seq Scan on kaifa_load_profiles_before_201901 t_7 (cost=0.00..10.71 rows=1 width=492) (actual time=0.003..0.003 rows=0 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
  • Rows Removed by Filter: 1
24. 16,524.357 16,524.357 ↓ 0.0 0 3

Parallel Seq Scan on kaifa_load_profiles_201901 t_8 (cost=0.00..1,177,257.94 rows=3,500 width=378) (actual time=16,524.357..16,524.357 rows=0 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
  • Rows Removed by Filter: 13852581
25. 32,640.941 32,640.941 ↓ 0.0 0 3

Parallel Seq Scan on kaifa_load_profiles_201902 t_9 (cost=0.00..2,055,905.24 rows=3,839 width=377) (actual time=32,640.941..32,640.941 rows=0 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
  • Rows Removed by Filter: 25007875
26. 42,770.961 42,770.961 ↓ 0.0 0 3

Parallel Seq Scan on kaifa_load_profiles_201903 t_10 (cost=0.00..2,922,415.23 rows=5,390 width=378) (actual time=42,770.961..42,770.961 rows=0 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
  • Rows Removed by Filter: 35776400
27. 79,347.710 79,347.710 ↓ 0.0 0 3

Parallel Seq Scan on kaifa_load_profiles_201904 t_11 (cost=0.00..3,143,047.88 rows=5,940 width=378) (actual time=79,347.710..79,347.710 rows=0 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
  • Rows Removed by Filter: 38475871
28. 24,363.414 24,363.414 ↑ 1.3 4,548 3

Parallel Seq Scan on kaifa_load_profiles_201905 t_12 (cost=0.00..1,314,494.71 rows=5,851 width=378) (actual time=24,347.678..24,363.414 rows=4,548 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
  • Rows Removed by Filter: 16085751
29. 0.000 0.000 ↓ 0.0 0 3

Parallel Seq Scan on kaifa_load_profiles_201906 t_13 (cost=0.00..10.71 rows=1 width=492) (actual time=0.000..0.000 rows=0 loops=3)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
30. 8.681 372,377.859 ↑ 1.2 16,792 1

Merge Full Join (cost=3,546.10..3,935.26 rows=19,617 width=1,128) (actual time=372,368.259..372,377.859 rows=16,792 loops=1)

  • Merge Cond: ((t_quality.tq_source_file = t_energy.te_source_file) AND (t_quality.tq_meter_id = t_energy.te_meter_id) AND (t_quality.tq_frame_date = t_energy.te_frame_date))
31. 5.876 176,668.730 ↑ 1.5 12,968 1

Sort (cost=1,755.08..1,803.21 rows=19,252 width=496) (actual time=176,668.259..176,668.730 rows=12,968 loops=1)

  • Sort Key: t_quality.tq_source_file, t_quality.tq_meter_id, t_quality.tq_frame_date
  • Sort Method: quicksort Memory: 3829kB
32. 176,662.854 176,662.854 ↑ 1.5 12,968 1

CTE Scan on t_quality (cost=0.00..385.04 rows=19,252 width=496) (actual time=176,646.219..176,662.854 rows=12,968 loops=1)

33. 5.151 195,700.448 ↑ 1.4 13,637 1

Sort (cost=1,791.01..1,840.06 rows=19,617 width=496) (actual time=195,699.985..195,700.448 rows=13,637 loops=1)

  • Sort Key: t_energy.te_source_file, t_energy.te_meter_id, t_energy.te_frame_date
  • Sort Method: quicksort Memory: 2312kB
34. 195,695.297 195,695.297 ↑ 1.4 13,637 1

CTE Scan on t_energy (cost=0.00..392.34 rows=19,617 width=496) (actual time=195,680.616..195,695.297 rows=13,637 loops=1)