explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PyMx

Settings
# exclusive inclusive rows x rows loops node
1. 37.726 186.809 ↑ 19.6 3,455 1

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

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

CTE tmp1

3. 1.889 1.889 ↓ 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.014..1.889 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.439 149.083 ↑ 16.4 72,555 1

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

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

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

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

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

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

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

8. 11.621 100.432 ↓ 1.0 72,555 1

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

9. 19.985 88.811 ↓ 1.0 72,555 1

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

  • Group Key: a.smpid, a.channelid
10. 45.254 68.826 ↓ 1.0 72,555 1

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

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

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

12. 0.490 0.490 ↓ 1.0 3,455 1

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

13. 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)