explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gLAJH

Settings
# exclusive inclusive rows x rows loops node
1. 39.043 156.526 ↑ 19.6 3,455 1

GroupAggregate (cost=168,067.05..186,747.03 rows=67,800 width=58) (actual time=115.029..156.526 rows=3,455 loops=1)

  • Group Key: b.compdate, a.smpid, b.alarm, b.received
2.          

CTE tmp1

3. 2.395 2.395 ↓ 1.0 3,455 1

Index Scan using index_mesreg_2 on mesreg (cost=0.29..402.38 rows=3,388 width=26) (actual time=0.016..2.395 rows=3,455 loops=1)

  • Index Cond: ((smpdate >= '2020-06-01 00:00:00+02'::timestamp with time zone) AND (smpdate <= '2020-06-30 00:00:00+02'::timestamp with time zone))
  • Filter: (((paramtype)::text = ANY ('{FIL,UPL}'::text[])) AND (stationid = 4))
  • Rows Removed by Filter: 3,457
4. 17.696 117.483 ↑ 16.4 72,555 1

Sort (cost=167,664.67..170,636.75 rows=1,188,832 width=38) (actual time=114.965..117.483 rows=72,555 loops=1)

  • Sort Key: b.compdate, a.smpid, b.alarm, b.received
  • Sort Method: quicksort Memory: 8,741kB
5. 13.090 99.787 ↑ 16.4 72,555 1

Merge Join (cost=27,591.60..47,704.90 rows=1,188,832 width=38) (actual time=58.881..99.787 rows=72,555 loops=1)

  • Merge Cond: (a.smpid = b.smpid)
6. 22.123 83.027 ↓ 1.0 72,555 1

GroupAggregate (cost=27,325.20..28,728.78 rows=70,179 width=20) (actual time=57.594..83.027 rows=72,555 loops=1)

  • Group Key: a.smpid, a.channelid
7. 33.648 60.904 ↓ 1.0 72,555 1

Sort (cost=27,325.20..27,500.65 rows=70,179 width=20) (actual time=57.583..60.904 rows=72,555 loops=1)

  • Sort Key: a.smpid, a.channelid
  • Sort Method: quicksort Memory: 8,463kB
8. 6.596 27.256 ↓ 1.0 72,555 1

Nested Loop (cost=0.42..21,676.23 rows=70,179 width=20) (actual time=0.035..27.256 rows=72,555 loops=1)

9. 3.385 3.385 ↓ 1.0 3,455 1

CTE Scan on tmp1 b_1 (cost=0.00..67.76 rows=3,388 width=8) (actual time=0.019..3.385 rows=3,455 loops=1)

10. 17.275 17.275 ↑ 1.0 21 3,455

Index Scan using index_mesval_1 on mesval a (cost=0.42..6.17 rows=21 width=20) (actual time=0.002..0.005 rows=21 loops=3,455)

  • Index Cond: (smpid = b_1.smpid)
11. 3.032 3.670 ↓ 21.4 72,535 1

Sort (cost=266.40..274.87 rows=3,388 width=26) (actual time=1.281..3.670 rows=72,535 loops=1)

  • Sort Key: b.smpid
  • Sort Method: quicksort Memory: 366kB
12. 0.638 0.638 ↓ 1.0 3,455 1

CTE Scan on tmp1 b (cost=0.00..67.76 rows=3,388 width=26) (actual time=0.003..0.638 rows=3,455 loops=1)