explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Iqd : string, sans outputter file

Settings
# exclusive inclusive rows x rows loops node
1. 8.171 108.299 ↓ 4.7 22,577 1

Sort (cost=55,181.51..55,193.40 rows=4,758 width=1,032) (actual time=107.508..108.299 rows=22,577 loops=1)

  • Sort Key: (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: 4689kB
2.          

CTE t_quality

3. 1.195 37.715 ↓ 2.6 12,101 1

Subquery Scan on x (cost=26,084.17..26,602.15 rows=4,604 width=227) (actual time=30.255..37.715 rows=12,101 loops=1)

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

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

5. 24.265 30.765 ↑ 1.1 12,130 1

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

  • Sort Key: t.meter_id, t.frame_date, t.num_ligne DESC
  • Sort Method: quicksort Memory: 3607kB
6. 6.500 6.500 ↑ 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=0.053..6.500 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.275 37.421 ↓ 2.7 12,844 1

Subquery Scan on x_1 (cost=26,955.50..27,455.05 rows=4,758 width=326) (actual time=29.879..37.421 rows=12,844 loops=1)

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

WindowAgg (cost=26,955.50..27,276.64 rows=14,273 width=330) (actual time=29.878..36.146 rows=12,860 loops=1)

10. 24.673 30.374 ↑ 1.1 12,860 1

Sort (cost=26,955.50..26,991.18 rows=14,273 width=322) (actual time=29.869..30.374 rows=12,860 loops=1)

  • Sort Key: t_1.meter_id, t_1.frame_date, t_1.num_ligne DESC
  • Sort Method: quicksort Memory: 2022kB
11. 5.701 5.701 ↑ 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=322) (actual time=0.049..5.701 rows=12,860 loops=1)

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

Merge Full Join (cost=757.98..833.68 rows=4,758 width=1,032) (actual time=92.560..100.128 rows=22,577 loops=1)

  • Merge Cond: ((t_quality.tq_meter_id = t_energy.te_meter_id) AND (t_quality.tq_frame_date = t_energy.te_frame_date))
13. 3.830 47.103 ↓ 2.6 12,101 1

Sort (cost=372.20..383.71 rows=4,604 width=464) (actual time=46.721..47.103 rows=12,101 loops=1)

  • Sort Key: t_quality.tq_meter_id, t_quality.tq_frame_date
  • Sort Method: quicksort Memory: 2086kB
14. 43.273 43.273 ↓ 2.6 12,101 1

CTE Scan on t_quality (cost=0.00..92.08 rows=4,604 width=464) (actual time=30.257..43.273 rows=12,101 loops=1)

15. 3.680 46.240 ↓ 2.7 12,844 1

Sort (cost=385.78..397.68 rows=4,758 width=464) (actual time=45.832..46.240 rows=12,844 loops=1)

  • Sort Key: t_energy.te_meter_id, t_energy.te_frame_date
  • Sort Method: quicksort Memory: 2191kB
16. 42.560 42.560 ↓ 2.7 12,844 1

CTE Scan on t_energy (cost=0.00..95.16 rows=4,758 width=464) (actual time=29.881..42.560 rows=12,844 loops=1)