explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XWs7

Settings
# exclusive inclusive rows x rows loops node
1. 48.108 198.693 ↑ 19.6 3,455 1

GroupAggregate (cost=233,139.59..251,837.11 rows=67,800 width=58) (actual time=143.757..198.693 rows=3,455 loops=1)

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

CTE tmp1

3. 2.017 2.017 ↓ 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.021..2.017 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. 35.016 150.585 ↑ 16.4 72,555 1

Sort (cost=232,737.21..235,712.22 rows=1,190,001 width=38) (actual time=143.663..150.585 rows=72,555 loops=1)

  • Sort Key: b.compdate, a.smpid, b.alarm, b.received
  • Sort Method: external merge Disk: 4,016kB
5. 10.728 115.569 ↑ 16.4 72,555 1

Merge Join (cost=27,597.66..47,572.05 rows=1,190,001 width=38) (actual time=64.708..115.569 rows=72,555 loops=1)

  • Merge Cond: (b.smpid = a.smpid)
6. 0.665 3.671 ↓ 1.0 3,455 1

Sort (cost=266.40..274.87 rows=3,388 width=26) (actual time=3.481..3.671 rows=3,455 loops=1)

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

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

8. 12.054 101.170 ↓ 1.0 72,555 1

Materialize (cost=27,331.25..29,614.31 rows=70,248 width=20) (actual time=61.221..101.170 rows=72,555 loops=1)

9. 21.952 89.116 ↓ 1.0 72,555 1

GroupAggregate (cost=27,331.25..28,736.21 rows=70,248 width=20) (actual time=61.218..89.116 rows=72,555 loops=1)

  • Group Key: a.smpid, a.channelid
10. 45.830 67.164 ↓ 1.0 72,555 1

Sort (cost=27,331.25..27,506.87 rows=70,248 width=20) (actual time=61.205..67.164 rows=72,555 loops=1)

  • Sort Key: a.smpid, a.channelid
  • Sort Method: external merge Disk: 2,320kB
11. 7.145 21.334 ↓ 1.0 72,555 1

Nested Loop (cost=0.42..21,676.23 rows=70,248 width=20) (actual time=0.011..21.334 rows=72,555 loops=1)

12. 0.369 0.369 ↓ 1.0 3,455 1

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

13. 13.820 13.820 ↑ 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.004 rows=21 loops=3,455)

  • Index Cond: (smpid = tmp1.smpid)