explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R44v

Settings
# exclusive inclusive rows x rows loops node
1. 9.386 139.145 ↑ 1.2 16,792 1

Sort (cost=294,355.90..294,404.80 rows=19,557 width=1,128) (actual time=138.124..139.145 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.381 54.256 ↑ 1.5 12,968 1

Subquery Scan on x (cost=141,465.36..143,624.13 rows=19,189 width=283) (actual time=46.185..54.256 rows=12,968 loops=1)

  • Filter: (x.r < 2)
  • Rows Removed by Filter: 6
4. 6.205 52.875 ↑ 4.4 12,974 1

WindowAgg (cost=141,465.36..142,904.54 rows=57,567 width=287) (actual time=46.183..52.875 rows=12,974 loops=1)

5. 29.893 46.670 ↑ 4.4 12,974 1

Sort (cost=141,465.36..141,609.28 rows=57,567 width=279) (actual time=46.170..46.670 rows=12,974 loops=1)

  • Sort Key: t.meter_id, t.frame_date, t.num_ligne DESC
  • Sort Method: quicksort Memory: 3831kB
6. 1.506 16.777 ↑ 4.4 12,974 1

Result (cost=0.00..136,913.84 rows=57,567 width=279) (actual time=1.571..16.777 rows=12,974 loops=1)

7. 0.673 15.271 ↑ 4.4 12,974 1

Append (cost=0.00..136,338.17 rows=57,567 width=279) (actual time=1.569..15.271 rows=12,974 loops=1)

8. 0.044 0.044 ↓ 0.0 0 1

Seq Scan on kaifa_load_profiles_before_201901 t (cost=0.00..11.20 rows=1 width=492) (actual time=0.044..0.044 rows=0 loops=1)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
  • Rows Removed by Filter: 2
9. 0.006 0.147 ↓ 0.0 0 1

Bitmap Heap Scan on kaifa_load_profiles_201901 t_1 (cost=437.06..31,000.54 rows=8,231 width=279) (actual time=0.147..0.147 rows=0 loops=1)

  • Recheck Cond: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
10. 0.141 0.141 ↓ 0.0 0 1

Bitmap Index Scan on source_file_type_ligne_201901 (cost=0.00..435.00 rows=8,231 width=0) (actual time=0.141..0.141 rows=0 loops=1)

  • Index Cond: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
11. 0.149 0.149 ↓ 0.0 0 1

Index Scan using source_file_type_ligne_201902 on kaifa_load_profiles_201902 t_2 (cost=0.69..31,769.97 rows=9,229 width=278) (actual time=0.149..0.149 rows=0 loops=1)

  • Index Cond: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
12. 0.176 0.176 ↓ 0.0 0 1

Index Scan using source_file_type_ligne_201903 on kaifa_load_profiles_201903 t_3 (cost=0.69..23,294.57 rows=12,436 width=279) (actual time=0.176..0.176 rows=0 loops=1)

  • Index Cond: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
13. 0.188 0.188 ↓ 0.0 0 1

Index Scan using source_file_type_ligne_201904 on kaifa_load_profiles_201904 t_4 (cost=0.69..25,134.27 rows=13,813 width=279) (actual time=0.188..0.188 rows=0 loops=1)

  • Index Cond: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
14. 13.892 13.892 ↑ 1.1 12,974 1

Index Scan using source_file_type_ligne on kaifa_load_profiles_201905 t_5 (cost=0.69..25,116.44 rows=13,856 width=279) (actual time=0.861..13.892 rows=12,974 loops=1)

  • Index Cond: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
15. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on kaifa_load_profiles_201906 t_6 (cost=0.00..11.20 rows=1 width=492) (actual time=0.002..0.002 rows=0 loops=1)

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

CTE t_energy

17. 1.503 44.239 ↑ 1.4 13,637 1

Subquery Scan on x_1 (cost=143,215.77..145,415.90 rows=19,557 width=382) (actual time=35.688..44.239 rows=13,637 loops=1)

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

WindowAgg (cost=143,215.77..144,682.52 rows=58,670 width=386) (actual time=35.687..42.736 rows=13,644 loops=1)

19. 25.767 36.154 ↑ 4.3 13,644 1

Sort (cost=143,215.77..143,362.45 rows=58,670 width=378) (actual time=35.683..36.154 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. 1.195 10.387 ↑ 4.3 13,644 1

Result (cost=0.00..138,569.01 rows=58,670 width=378) (actual time=0.571..10.387 rows=13,644 loops=1)

21. 0.587 9.192 ↑ 4.3 13,644 1

Append (cost=0.00..137,982.31 rows=58,670 width=378) (actual time=0.570..9.192 rows=13,644 loops=1)

22. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on kaifa_load_profiles_before_201901 t_7 (cost=0.00..11.20 rows=1 width=492) (actual time=0.011..0.011 rows=0 loops=1)

  • Filter: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
  • Rows Removed by Filter: 2
23. 0.003 0.029 ↓ 0.0 0 1

Bitmap Heap Scan on kaifa_load_profiles_201901 t_8 (cost=427.56..30,474.16 rows=8,085 width=378) (actual time=0.029..0.029 rows=0 loops=1)

  • Recheck Cond: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
24. 0.026 0.026 ↓ 0.0 0 1

Bitmap Index Scan on source_file_type_ligne_201901 (cost=0.00..425.54 rows=8,085 width=0) (actual time=0.026..0.026 rows=0 loops=1)

  • Index Cond: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
25. 0.025 0.025 ↓ 0.0 0 1

Index Scan using source_file_type_ligne_201902 on kaifa_load_profiles_201902 t_9 (cost=0.69..31,517.17 rows=9,156 width=377) (actual time=0.025..0.025 rows=0 loops=1)

  • Index Cond: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
26. 0.024 0.024 ↓ 0.0 0 1

Index Scan using source_file_type_ligne_201903 on kaifa_load_profiles_201903 t_10 (cost=0.69..24,232.62 rows=12,939 width=378) (actual time=0.024..0.024 rows=0 loops=1)

  • Index Cond: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
27. 0.025 0.025 ↓ 0.0 0 1

Index Scan using source_file_type_ligne_201904 on kaifa_load_profiles_201904 t_11 (cost=0.69..25,970.59 rows=14,273 width=378) (actual time=0.025..0.025 rows=0 loops=1)

  • Index Cond: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
28. 8.488 8.488 ↑ 1.0 13,644 1

Index Scan using source_file_type_ligne on kaifa_load_profiles_201905 t_12 (cost=0.69..25,765.37 rows=14,215 width=378) (actual time=0.454..8.488 rows=13,644 loops=1)

  • Index Cond: ((source_file = '/srv/acquisition/LP_20190513175000_26618_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
29. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on kaifa_load_profiles_201906 t_13 (cost=0.00..11.20 rows=1 width=492) (actual time=0.003..0.003 rows=0 loops=1)

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

Merge Full Join (cost=3,533.99..3,921.92 rows=19,557 width=1,128) (actual time=120.377..129.759 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.553 66.697 ↑ 1.5 12,968 1

Sort (cost=1,748.88..1,796.86 rows=19,189 width=496) (actual time=66.251..66.697 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. 61.144 61.144 ↑ 1.5 12,968 1

CTE Scan on t_quality (cost=0.00..383.78 rows=19,189 width=496) (actual time=46.189..61.144 rows=12,968 loops=1)

33. 4.821 54.537 ↑ 1.4 13,637 1

Sort (cost=1,785.10..1,834.00 rows=19,557 width=496) (actual time=54.112..54.537 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. 49.716 49.716 ↑ 1.4 13,637 1

CTE Scan on t_energy (cost=0.00..391.14 rows=19,557 width=496) (actual time=35.691..49.716 rows=13,637 loops=1)

Planning time : 11.562 ms
Execution time : 142.351 ms