explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PuxM

Settings
# exclusive inclusive rows x rows loops node
1. 1.540 98.452 ↑ 177.1 3,455 1

Sort (cost=111,435.06..112,965.17 rows=612,042 width=58) (actual time=98.293..98.452 rows=3,455 loops=1)

  • Sort Key: b.compdate
  • Sort Method: quicksort Memory: 1,878kB
2.          

CTE tmp1

3. 2.158 2.158 ↓ 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.023..2.158 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. 1.160 96.912 ↑ 177.1 3,455 1

Hash Join (cost=29,116.09..29,192.94 rows=612,042 width=58) (actual time=95.610..96.912 rows=3,455 loops=1)

  • Hash Cond: (b.smpid = a.smpid)
5. 0.408 0.408 ↓ 1.0 3,455 1

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

6. 1.320 95.344 ↑ 10.5 3,455 1

Hash (cost=28,664.47..28,664.47 rows=36,130 width=40) (actual time=95.344..95.344 rows=3,455 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,615kB
7. 38.222 94.024 ↑ 10.5 3,455 1

GroupAggregate (cost=27,325.20..28,303.17 rows=36,130 width=40) (actual time=50.068..94.024 rows=3,455 loops=1)

  • Group Key: a.smpid
8. 31.318 55.802 ↓ 1.0 72,555 1

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

  • Sort Key: a.smpid
  • Sort Method: external merge Disk: 2,320kB
9. 7.608 24.484 ↓ 1.0 72,555 1

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

10. 3.056 3.056 ↓ 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..3.056 rows=3,455 loops=1)

11. 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 = b_1.smpid)