explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BhiT

Settings
# exclusive inclusive rows x rows loops node
1. 10.539 133.642 ↓ 4.7 22,577 1

Sort (cost=55,235.15..55,247.04 rows=4,758 width=1,128) (actual time=132.733..133.642 rows=22,577 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: 7358kB
2.          

CTE t_quality

3. 1.288 54.620 ↓ 2.6 12,101 1

Subquery Scan on x (cost=26,084.17..26,602.15 rows=4,604 width=283) (actual time=47.040..54.620 rows=12,101 loops=1)

  • Filter: (x.r < 2)
  • Rows Removed by Filter: 29
4. 5.829 53.332 ↑ 1.1 12,130 1

WindowAgg (cost=26,084.17..26,429.49 rows=13,813 width=287) (actual time=47.037..53.332 rows=12,130 loops=1)

5. 29.165 47.503 ↑ 1.1 12,130 1

Sort (cost=26,084.17..26,118.70 rows=13,813 width=279) (actual time=47.026..47.503 rows=12,130 loops=1)

  • Sort Key: t.meter_id, t.frame_date, t.num_ligne DESC
  • Sort Method: quicksort Memory: 3607kB
6. 18.338 18.338 ↑ 1.1 12,130 1

Index Scan using source_file_type_ligne_201904 on kaifa_load_profiles_201904 t (cost=0.69..25,134.27 rows=13,813 width=279) (actual time=1.120..18.338 rows=12,130 loops=1)

  • Index Cond: ((source_file = '/srv/acquisition/LP_20190429223000_24990_0-numerote.txt'::text) AND (type_ligne = 'QUALITY'::text))
7.          

CTE t_energy

8. 1.319 38.989 ↓ 2.7 12,844 1

Subquery Scan on x_1 (cost=26,955.50..27,490.74 rows=4,758 width=382) (actual time=30.821..38.989 rows=12,844 loops=1)

  • Filter: (x_1.r < 2)
  • Rows Removed by Filter: 16
9. 6.418 37.670 ↑ 1.1 12,860 1

WindowAgg (cost=26,955.50..27,312.32 rows=14,273 width=386) (actual time=30.820..37.670 rows=12,860 loops=1)

10. 24.824 31.252 ↑ 1.1 12,860 1

Sort (cost=26,955.50..26,991.18 rows=14,273 width=378) (actual time=30.814..31.252 rows=12,860 loops=1)

  • Sort Key: t_1.meter_id, t_1.frame_date, t_1.num_ligne DESC
  • Sort Method: quicksort Memory: 2193kB
11. 6.428 6.428 ↑ 1.1 12,860 1

Index Scan using source_file_type_ligne_201904 on kaifa_load_profiles_201904 t_1 (cost=0.69..25,970.59 rows=14,273 width=378) (actual time=0.403..6.428 rows=12,860 loops=1)

  • Index Cond: ((source_file = '/srv/acquisition/LP_20190429223000_24990_0-numerote.txt'::text) AND (type_ligne = 'ENERGY'::text))
12. 8.001 123.103 ↓ 4.7 22,577 1

Merge Full Join (cost=757.98..851.63 rows=4,758 width=1,128) (actual time=114.277..123.103 rows=22,577 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))
13. 5.228 66.323 ↓ 2.6 12,101 1

Sort (cost=372.20..383.71 rows=4,604 width=496) (actual time=65.897..66.323 rows=12,101 loops=1)

  • Sort Key: t_quality.tq_source_file, t_quality.tq_meter_id, t_quality.tq_frame_date
  • Sort Method: quicksort Memory: 3599kB
14. 61.095 61.095 ↓ 2.6 12,101 1

CTE Scan on t_quality (cost=0.00..92.08 rows=4,604 width=496) (actual time=47.043..61.095 rows=12,101 loops=1)

15. 4.627 48.779 ↓ 2.7 12,844 1

Sort (cost=385.78..397.68 rows=4,758 width=496) (actual time=48.371..48.779 rows=12,844 loops=1)

  • Sort Key: t_energy.te_source_file, t_energy.te_meter_id, t_energy.te_frame_date
  • Sort Method: quicksort Memory: 2202kB
16. 44.152 44.152 ↓ 2.7 12,844 1

CTE Scan on t_energy (cost=0.00..95.16 rows=4,758 width=496) (actual time=30.824..44.152 rows=12,844 loops=1)

Planning time : 2.040 ms
Execution time : 136.448 ms