explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4VDl

Settings
# exclusive inclusive rows x rows loops node
1. 0.882 51.114 ↑ 78.7 3,455 1

Sort (cost=48,415.13..49,095.14 rows=272,006 width=58) (actual time=50.939..51.114 rows=3,455 loops=1)

  • Sort Key: b.compdate
  • Sort Method: quicksort Memory: 582kB
2.          

CTE tmp1

3. 2.858 2.858 ↓ 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.029..2.858 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.132 50.232 ↑ 78.7 3,455 1

Hash Join (cost=23,382.90..23,459.75 rows=272,006 width=58) (actual time=48.862..50.232 rows=3,455 loops=1)

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

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

6. 0.766 48.737 ↑ 4.6 3,455 1

Hash (cost=23,182.19..23,182.19 rows=16,057 width=40) (actual time=48.737..48.737 rows=3,455 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 517kB
7. 13.041 47.971 ↑ 4.6 3,455 1

GroupAggregate (cost=22,700.48..23,021.62 rows=16,057 width=40) (actual time=34.206..47.971 rows=3,455 loops=1)

  • Group Key: a.smpid
8. 4.206 34.930 ↓ 1.1 17,275 1

Sort (cost=22,700.48..22,740.62 rows=16,057 width=20) (actual time=34.156..34.930 rows=17,275 loops=1)

  • Sort Key: a.smpid
  • Sort Method: quicksort Memory: 1,840kB
9. 2.513 30.724 ↓ 1.1 17,275 1

Nested Loop (cost=0.42..21,578.83 rows=16,057 width=20) (actual time=0.026..30.724 rows=17,275 loops=1)

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

11. 24.185 24.185 ↑ 1.0 5 3,455

Index Scan using index_mesval_1 on mesval a (cost=0.42..6.30 rows=5 width=20) (actual time=0.005..0.007 rows=5 loops=3,455)

  • Index Cond: (smpid = b_1.smpid)
  • Filter: (channelid = ANY ('{208,209,210,211,212}'::integer[]))
  • Rows Removed by Filter: 16