explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OUfb

Settings
# exclusive inclusive rows x rows loops node
1. 43.156 244.530 ↑ 19.6 3,455 1

GroupAggregate (cost=232,924.54..251,604.52 rows=67,800 width=58) (actual time=194.698..244.530 rows=3,455 loops=1)

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

CTE tmp1

3. 2.237 2.237 ↓ 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.017..2.237 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. 41.759 201.374 ↑ 16.4 72,555 1

Sort (cost=232,522.16..235,494.24 rows=1,188,832 width=38) (actual time=194.658..201.374 rows=72,555 loops=1)

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

Merge Join (cost=27,591.60..47,546.39 rows=1,188,832 width=38) (actual time=88.076..159.615 rows=72,555 loops=1)

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

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

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

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

8. 15.456 140.403 ↓ 1.0 72,555 1

Materialize (cost=27,325.20..29,606.02 rows=70,179 width=20) (actual time=84.234..140.403 rows=72,555 loops=1)

9. 31.680 124.947 ↓ 1.0 72,555 1

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

  • Group Key: a.smpid, a.channelid
10. 62.352 93.267 ↓ 1.0 72,555 1

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

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

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

12. 0.728 0.728 ↓ 1.0 3,455 1

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

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

  • Index Cond: (smpid = b_1.smpid)