explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aJ2K

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 397.916 ↑ 1.0 1 1

Result (cost=4,845.29..4,845.30 rows=1 width=16) (actual time=397.915..397.916 rows=1 loops=1)

2.          

CTE q_params

3. 0.017 0.017 ↑ 1.0 1 1

Result (cost=0.00..0.02 rows=1 width=48) (actual time=0.017..0.017 rows=1 loops=1)

4.          

CTE scanned_sockets

5. 0.899 133.761 ↓ 33.2 3,323 1

Nested Loop (cost=0.00..2.02 rows=100 width=200) (actual time=128.716..133.761 rows=3,323 loops=1)

6. 21.554 21.554 ↑ 1.0 1 1

CTE Scan on "q_params" "qp" (cost=0.00..0.02 rows=1 width=32) (actual time=21.552..21.554 rows=1 loops=1)

7. 111.308 111.308 ↓ 33.2 3,323 1

Function Scan on "jsonb_populate_recordset" "ic" (cost=0.00..1.00 rows=100 width=200) (actual time=107.155..111.308 rows=3,323 loops=1)

8.          

CTE inserted_sockets

9. 190.845 373.027 ↓ 33.2 3,323 1

Insert on "sockets" (cost=0.00..3.02 rows=100 width=200) (actual time=167.390..373.027 rows=3,323 loops=1)

  • Conflict Resolution: NOTHING
  • Conflict Arbiter Indexes: pk_sockets
  • Tuples Inserted: 3323
  • Conflicting Tuples: 0
10. 0.810 182.182 ↓ 33.2 3,323 1

Nested Loop (cost=0.00..3.02 rows=100 width=200) (actual time=165.759..182.182 rows=3,323 loops=1)

11. 37.030 37.030 ↑ 1.0 1 1

CTE Scan on "q_params" "qp_1" (cost=0.00..0.02 rows=1 width=0) (actual time=37.030..37.030 rows=1 loops=1)

12. 144.342 144.342 ↓ 33.2 3,323 1

CTE Scan on "scanned_sockets" "ic_1" (cost=0.00..2.00 rows=100 width=200) (actual time=128.726..144.342 rows=3,323 loops=1)

13.          

CTE locked_sockets

14. 0.002 12.426 ↓ 0.0 0 1

LockRows (cost=2,057.26..2,105.93 rows=3,894 width=157) (actual time=12.426..12.426 rows=0 loops=1)

15. 0.043 12.424 ↓ 0.0 0 1

Sort (cost=2,057.26..2,066.99 rows=3,894 width=157) (actual time=12.424..12.424 rows=0 loops=1)

  • Sort Key: "ls"."ts_snapshot", "ls"."host_name", "ls"."local_address", "ls"."local_port
  • Sort Method: quicksort Memory: 25kB
16. 0.001 12.381 ↓ 0.0 0 1

Hash Join (cost=3.25..1,825.04 rows=3,894 width=157) (actual time=12.381..12.381 rows=0 loops=1)

  • Hash Cond: (("ls"."host_name")::"text" = ("ss"."host_name")::"text")
17. 1.418 12.380 ↓ 0.0 0 1

Nested Loop (cost=0.00..1,768.25 rows=3,894 width=101) (actual time=12.380..12.380 rows=0 loops=1)

  • Join Filter: ("ls"."ts_snapshot" < ("transaction_timestamp"() - "qp_2"."socket_retention_window"))
  • Rows Removed by Join Filter: 12178
18. 5.628 5.628 ↑ 1.0 1 1

CTE Scan on "q_params" "qp_2" (cost=0.00..0.02 rows=1 width=56) (actual time=5.621..5.628 rows=1 loops=1)

19. 5.334 5.334 ↓ 1.0 12,178 1

Seq Scan on "sockets" "ls" (cost=0.00..1,563.81 rows=11,681 width=61) (actual time=0.044..5.334 rows=12,178 loops=1)

20. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.00..2.00 rows=100 width=88) (never executed)

21. 0.000 0.000 ↓ 0.0 0

CTE Scan on "scanned_sockets" "ss" (cost=0.00..2.00 rows=100 width=88) (never executed)

22.          

CTE deleted_sockets

23. 0.002 12.431 ↓ 0.0 0 1

Delete on "sockets" "ds" (cost=1,797.43..2,732.00 rows=1 width=182) (actual time=12.431..12.431 rows=0 loops=1)

24. 0.002 12.429 ↓ 0.0 0 1

Hash Join (cost=1,797.43..2,732.00 rows=1 width=182) (actual time=12.429..12.429 rows=0 loops=1)

  • Hash Cond: (("ls_1"."ts_snapshot" = "ds"."ts_snapshot") AND (("ls_1"."host_name")::"text" = ("ds"."host_name")::"text") AND ("ls_1"."local_address" = "ds"."local_address") AND (("ls_1"."local_port")::integer = ("ds"."local_port")::integer))
25. 12.427 12.427 ↓ 0.0 0 1

CTE Scan on "locked_sockets" "ls_1" (cost=0.00..77.88 rows=3,894 width=176) (actual time=12.427..12.427 rows=0 loops=1)

26. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,563.81..1,563.81 rows=11,681 width=61) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Seq Scan on "sockets" "ds" (cost=0.00..1,563.81 rows=11,681 width=61) (never executed)

28.          

Initplan (forResult)

29. 0.533 385.472 ↑ 1.0 1 1

Aggregate (cost=2.25..2.26 rows=1 width=8) (actual time=385.472..385.472 rows=1 loops=1)

30. 384.939 384.939 ↓ 33.2 3,323 1

CTE Scan on "inserted_sockets" (cost=0.00..2.00 rows=100 width=0) (actual time=167.399..384.939 rows=3,323 loops=1)

31. 0.005 12.437 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=12.437..12.437 rows=1 loops=1)

32. 12.432 12.432 ↓ 0.0 0 1

CTE Scan on "deleted_sockets" (cost=0.00..0.02 rows=1 width=0) (actual time=12.432..12.432 rows=0 loops=1)