explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8K0K

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 4,362.207 ↑ 1.0 101 1

Limit (cost=1,224,666.04..1,224,666.29 rows=101 width=24) (actual time=4,362.193..4,362.207 rows=101 loops=1)

2.          

CTE current_userroles

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

4. 79.879 4,362.199 ↑ 91.6 101 1

Sort (cost=1,224,666.03..1,224,689.16 rows=9,252 width=24) (actual time=4,362.191..4,362.199 rows=101 loops=1)

  • Sort Key: thread.date_updated DESC NULLS LAST, thread.id DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 32kB
5. 722.924 4,282.320 ↓ 100.0 925,169 1

HashAggregate (cost=1,224,219.24..1,224,311.76 rows=9,252 width=24) (actual time=4,040.724..4,282.320 rows=925,169 loops=1)

  • Group Key: thread.id
6. 1,516.628 3,559.396 ↓ 100.0 925,169 1

Nested Loop (cost=0.00..1,224,196.11 rows=9,252 width=24) (actual time=0.562..3,559.396 rows=925,169 loops=1)

  • Join Filter: ((SubPlan 2) && current_userroles.roles)
7. 0.008 0.008 ↑ 1.0 1 1

CTE Scan on current_userroles (cost=0.00..0.02 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=1)

8. 192.422 192.422 ↑ 1.0 925,169 1

Seq Scan on thread (cost=0.00..42,292.69 rows=925,169 width=105) (actual time=0.496..192.422 rows=925,169 loops=1)

  • Filter: (NOT anonymized)
9.          

SubPlan (for Nested Loop)

10. 925.169 1,850.338 ↑ 1.0 1 925,169

Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=925,169)

11. 925.169 925.169 ↑ 25.0 4 925,169

Function Scan on jsonb_array_elements_text x (cost=0.00..1.00 rows=100 width=32) (actual time=0.001..0.001 rows=4 loops=925,169)