explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TMIG

Settings
# exclusive inclusive rows x rows loops node
1. 9.776 114.143 ↓ 5.0 22,484 1

Sort (cost=103,376.49..103,387.82 rows=4,532 width=1,056) (actual time=113.139..114.143 rows=22,484 loops=1)

  • Sort Key: (COALESCE(t_energy.te_source_fileid, t_quality.tq_source_fileid)), (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: 5395kB
2.          

CTE t_quality

3. 1.217 40.001 ↓ 2.7 11,733 1

Subquery Scan on x (cost=49,206.44..49,686.86 rows=4,270 width=235) (actual time=32.579..40.001 rows=11,733 loops=1)

  • Filter: (x.r < 2)
  • Rows Removed by Filter: 36
4. 5.806 38.784 ↑ 1.1 11,769 1

WindowAgg (cost=49,206.44..49,526.72 rows=12,811 width=239) (actual time=32.577..38.784 rows=11,769 loops=1)

5. 23.397 32.978 ↑ 1.1 11,769 1

Sort (cost=49,206.44..49,238.47 rows=12,811 width=231) (actual time=32.566..32.978 rows=11,769 loops=1)

  • Sort Key: t.meter_id, t.frame_date, t.num_ligne DESC
  • Sort Method: quicksort Memory: 2040kB
6. 5.401 9.581 ↑ 1.1 11,769 1

Bitmap Heap Scan on zzz_kaifa_lp t (cost=375.88..48,332.41 rows=12,811 width=231) (actual time=4.327..9.581 rows=11,769 loops=1)

  • Recheck Cond: ((source_fileid = 4001) AND (type_ligne = 'QUALITY'::text))
  • Heap Blocks: exact=277
7. 4.180 4.180 ↑ 1.1 11,769 1

Bitmap Index Scan on zzz_kaifa_lp_source_file_type_ligne_idx (cost=0.00..372.68 rows=12,811 width=0) (actual time=4.180..4.180 rows=11,769 loops=1)

  • Index Cond: ((source_fileid = 4001) AND (type_ligne = 'QUALITY'::text))
8.          

CTE t_energy

9. 1.334 36.966 ↓ 2.8 12,904 1

Subquery Scan on x_1 (cost=52,107.76..52,617.61 rows=4,532 width=334) (actual time=29.165..36.966 rows=12,904 loops=1)

  • Filter: (x_1.r < 2)
  • Rows Removed by Filter: 16
10. 5.975 35.632 ↑ 1.1 12,920 1

WindowAgg (cost=52,107.76..52,447.66 rows=13,596 width=338) (actual time=29.163..35.632 rows=12,920 loops=1)

11. 25.209 29.657 ↑ 1.1 12,920 1

Sort (cost=52,107.76..52,141.75 rows=13,596 width=330) (actual time=29.160..29.657 rows=12,920 loops=1)

  • Sort Key: t_1.meter_id, t_1.frame_date, t_1.num_ligne DESC
  • Sort Method: quicksort Memory: 2201kB
12. 3.487 4.448 ↑ 1.1 12,920 1

Bitmap Heap Scan on zzz_kaifa_lp t_1 (cost=395.93..51,174.34 rows=13,596 width=330) (actual time=0.993..4.448 rows=12,920 loops=1)

  • Recheck Cond: ((source_fileid = 4001) AND (type_ligne = 'ENERGY'::text))
  • Heap Blocks: exact=334
13. 0.961 0.961 ↑ 1.1 12,920 1

Bitmap Index Scan on zzz_kaifa_lp_source_file_type_ligne_idx (cost=0.00..392.53 rows=13,596 width=0) (actual time=0.961..0.961 rows=12,920 loops=1)

  • Index Cond: ((source_fileid = 4001) AND (type_ligne = 'ENERGY'::text))
14. 7.826 104.367 ↓ 5.0 22,484 1

Merge Full Join (cost=708.75..796.79 rows=4,532 width=1,056) (actual time=95.637..104.367 rows=22,484 loops=1)

  • Merge Cond: ((t_quality.tq_source_fileid = t_energy.te_source_fileid) AND (t_quality.tq_meter_id = t_energy.te_meter_id) AND (t_quality.tq_frame_date = t_energy.te_frame_date))
15. 4.249 49.605 ↓ 2.7 11,733 1

Sort (cost=342.88..353.56 rows=4,270 width=472) (actual time=49.139..49.605 rows=11,733 loops=1)

  • Sort Key: t_quality.tq_source_fileid, t_quality.tq_meter_id, t_quality.tq_frame_date
  • Sort Method: quicksort Memory: 2034kB
16. 45.356 45.356 ↓ 2.7 11,733 1

CTE Scan on t_quality (cost=0.00..85.40 rows=4,270 width=472) (actual time=32.582..45.356 rows=11,733 loops=1)

17. 4.245 46.936 ↓ 2.8 12,904 1

Sort (cost=365.87..377.20 rows=4,532 width=472) (actual time=46.490..46.936 rows=12,904 loops=1)

  • Sort Key: t_energy.te_source_fileid, t_energy.te_meter_id, t_energy.te_frame_date
  • Sort Method: quicksort Memory: 2199kB
18. 42.691 42.691 ↓ 2.8 12,904 1

CTE Scan on t_energy (cost=0.00..90.64 rows=4,532 width=472) (actual time=29.167..42.691 rows=12,904 loops=1)