explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mMvi : Optimization for: plan #YLyD

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.337 3,398.228 ↑ 1.0 1 1

Aggregate (cost=988,541.54..988,541.55 rows=1 width=8) (actual time=3,398.228..3,398.228 rows=1 loops=1)

2. 71.167 3,396.891 ↑ 1.8 20,011 1

Unique (cost=985,477.18..988,103.78 rows=35,021 width=1,369) (actual time=3,209.398..3,396.891 rows=20,011 loops=1)

3. 576.842 3,325.724 ↑ 1.0 350,013 1

Sort (cost=985,477.18..986,352.72 rows=350,213 width=1,369) (actual time=3,209.397..3,325.724 rows=350,013 loops=1)

  • Sort Key: tempview.clientmac, tempview.tenantid, tempview.disconnectedat DESC, tempview.connectedat DESC
  • Sort Method: external merge Disk: 16256kB
4. 227.408 2,748.882 ↑ 1.0 350,013 1

Subquery Scan on tempview (cost=497,496.93..534,269.29 rows=350,213 width=1,369) (actual time=1,972.048..2,748.882 rows=350,013 loops=1)

5. 337.549 2,521.474 ↑ 1.0 350,013 1

Unique (cost=497,496.93..530,767.16 rows=350,213 width=1,369) (actual time=1,972.044..2,521.474 rows=350,013 loops=1)

6. 841.078 2,183.925 ↑ 1.0 350,013 1

Sort (cost=497,496.93..498,372.46 rows=350,213 width=1,369) (actual time=1,972.042..2,183.925 rows=350,013 loops=1)

  • Sort Key: activeradiussessionvm0.accountsessionid, activeradiussessionvm0.username, activeradiussessionvm0.zonewlaninfo, (concat_ws('.'::text, get_byte(activeradiussessionvm0.stationip, 0), get_byte(activeradiussessionvm0.stationip, 1), get_byte(activeradiussessionvm0.stationip, 2), get_byte(activeradiussessionvm0.stationip, 3))), (concat_ws(':'::text, encode("substring"(activeradiussessionvm0.stationipv6, 1, 2), 'hex'::text), encode("substring"(activeradiussessionvm0.stationipv6, 3, 2), 'hex'::text), encode("substring"(activeradiussessionvm0.stationipv6, 5, 2), 'hex'::text), encode("substring"(activeradiussessionvm0.stationipv6, 7, 2), 'hex'::text), encode("substring"(activeradiussessionvm0.stationipv6, 9, 2), 'hex'::text), encode("substring"(activeradiussessionvm0.stationipv6, 11, 2), 'hex'::text), encode("substring"(activeradiussessionvm0.stationipv6, 13, 2), 'hex'::text), encode("substring"(activeradiussessionvm0.stationipv6, 15, 2), 'hex'::text))), activeradiussessionvm0.stationmac, activeradiussessionvm0.epochinsertiontime, activeradiussessionvm0.sessionendtime, activeradiussessionvm0.fingerprintstamacvendor, activeradiussessionvm0.fingerprintstaosinfo, activeradiussessionvm0.fingerprintstadevicetype, activeradiussessionvm0.tenantname, activeradiussessionvm0.sessionstatus, activeradiussessionvm0.apname, activeradiussessionvm0.aptag, activeradiussessionvm0.apgrpname, activeradiussessionvm0.apmac, activeradiussessionvm0.apstate, activeradiussessionvm0.aplocation, activeradiussessionvm0.ssid, activeradiussessionvm0.nsnclientvlan, activeradiussessionvm0.zoneid, activeradiussessionvm0.zonename, activeradiussessionvm0.connectiontype, activeradiussessionvm0.authmethod, activeradiussessionvm0.wlanmac, activeradiussessionvm0.bandwidth, activeradiussessionvm0.throughput, activeradiussessionvm0.protocolname, activeradiussessionvm0.tenantid, ((date_part('epoch'::text, CURRENT_TIMESTAMP) - (activeradiussessionvm0.sessionstarttime)::double precision)), (0), (CASE WHEN (activeradiussessionvm0.smdeletedmarkedforarchive <> 0) THEN 2 WHEN (activeradiussessionvm0.sessionstatus = 8) THEN 0 WHEN (activeradiussessionvm0.sessionstatus = 2) THEN 3 ELSE 1 END), activeradiussessionvm0.client11accapability, activeradiussessionvm0.authservername, (COALESCE(activeradiussessionvm0.authenticationtime, 0)), (COALESCE(activeradiussessionvm0.dhcpduration, 0))
  • Sort Method: external merge Disk: 71816kB
7. 28.165 1,342.847 ↑ 1.0 350,013 1

Append (cost=0.00..46,289.03 rows=350,213 width=1,369) (actual time=0.027..1,342.847 rows=350,013 loops=1)

8. 1,104.400 1,314.680 ↑ 1.0 350,013 1

Result (cost=0.00..42,774.00 rows=350,173 width=1,164) (actual time=0.027..1,314.680 rows=350,013 loops=1)

9. 27.621 210.280 ↑ 1.0 350,013 1

Append (cost=0.00..13,884.73 rows=350,173 width=1,101) (actual time=0.009..210.280 rows=350,013 loops=1)

10. 90.386 90.386 ↑ 1.0 170,005 1

Seq Scan on activeradiussessionvm0 (cost=0.00..6,899.05 rows=170,005 width=1,100) (actual time=0.009..90.386 rows=170,005 loops=1)

11. 20.469 20.469 ↑ 1.0 40,002 1

Seq Scan on activeradiussessionvm1 (cost=0.00..1,543.02 rows=40,002 width=1,102) (actual time=0.014..20.469 rows=40,002 loops=1)

12. 20.558 20.558 ↑ 1.0 40,002 1

Seq Scan on activeradiussessionvm2 (cost=0.00..1,543.02 rows=40,002 width=1,102) (actual time=0.008..20.558 rows=40,002 loops=1)

13. 20.859 20.859 ↑ 1.0 40,002 1

Seq Scan on activeradiussessionvm3 (cost=0.00..1,543.02 rows=40,002 width=1,102) (actual time=0.008..20.859 rows=40,002 loops=1)

14. 20.221 20.221 ↑ 1.0 40,002 1

Seq Scan on activeradiussessionvm4 (cost=0.00..1,543.02 rows=40,002 width=1,102) (actual time=0.007..20.221 rows=40,002 loops=1)

15. 10.153 10.153 ↑ 1.0 20,000 1

Seq Scan on activeradiussessionvm5 (cost=0.00..772.00 rows=20,000 width=1,102) (actual time=0.013..10.153 rows=20,000 loops=1)

16. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on activeradiussessionvm6 (cost=0.00..10.40 rows=40 width=1,359) (actual time=0.003..0.003 rows=0 loops=1)

17. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on activeradiussessionvm7 (cost=0.00..10.40 rows=40 width=1,359) (actual time=0.002..0.002 rows=0 loops=1)

18. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on activeradiussessionvm8 (cost=0.00..10.40 rows=40 width=1,359) (actual time=0.002..0.002 rows=0 loops=1)

19. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on activeradiussessionvm9 (cost=0.00..10.40 rows=40 width=1,359) (actual time=0.006..0.006 rows=0 loops=1)

20. 0.000 0.002 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..13.30 rows=40 width=1,369) (actual time=0.002..0.002 rows=0 loops=1)

21. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on archiveradiussession arch (cost=0.00..12.80 rows=40 width=1,365) (actual time=0.002..0.002 rows=0 loops=1)