explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6q8i

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 17,733.150 ↓ 50.0 100 1

Limit (cost=1,140.01..1,140.02 rows=2 width=10) (actual time=17,733.140..17,733.150 rows=100 loops=1)

2. 0.033 17,733.146 ↓ 50.0 100 1

Sort (cost=1,140.01..1,140.02 rows=2 width=10) (actual time=17,733.140..17,733.146 rows=100 loops=1)

  • Sort Key: commands.priority DESC, commands.id
  • Sort Method: quicksort Memory: 29kB
3. 0.009 17,733.113 ↓ 50.0 100 1

Subquery Scan on commands (cost=1,139.97..1,140.00 rows=2 width=10) (actual time=17,733.086..17,733.113 rows=100 loops=1)

4. 0.017 17,733.104 ↓ 50.0 100 1

Unique (cost=1,139.97..1,139.98 rows=2 width=10) (actual time=17,733.086..17,733.104 rows=100 loops=1)

5. 0.032 17,733.087 ↓ 50.0 100 1

Sort (cost=1,139.97..1,139.97 rows=2 width=10) (actual time=17,733.082..17,733.087 rows=100 loops=1)

  • Sort Key: cq.id, cq.priority
  • Sort Method: quicksort Memory: 29kB
6. 0.008 17,733.055 ↓ 50.0 100 1

Append (cost=17.24..1,139.96 rows=2 width=10) (actual time=17,733.032..17,733.055 rows=100 loops=1)

7. 0.000 0.033 ↓ 0.0 0 1

Limit (cost=17.24..17.24 rows=1 width=10) (actual time=0.033..0.033 rows=0 loops=1)

8. 0.004 0.033 ↓ 0.0 0 1

Sort (cost=17.24..17.24 rows=1 width=10) (actual time=0.033..0.033 rows=0 loops=1)

  • Sort Key: cq.priority DESC, cq.id
  • Sort Method: quicksort Memory: 25kB
9. 0.000 0.029 ↓ 0.0 0 1

Nested Loop (cost=9.19..17.23 rows=1 width=10) (actual time=0.029..0.029 rows=0 loops=1)

10. 0.001 0.029 ↓ 0.0 0 1

HashAggregate (cost=8.63..8.64 rows=1 width=8) (actual time=0.029..0.029 rows=0 loops=1)

  • Group Key: min(cq1.id)
11. 0.001 0.028 ↓ 0.0 0 1

GroupAggregate (cost=8.59..8.61 rows=1 width=23) (actual time=0.028..0.028 rows=0 loops=1)

  • Group Key: cq1.target_id
12. 0.007 0.027 ↓ 0.0 0 1

Sort (cost=8.59..8.60 rows=1 width=23) (actual time=0.027..0.027 rows=0 loops=1)

  • Sort Key: cq1.target_id
  • Sort Method: quicksort Memory: 25kB
13. 0.020 0.020 ↓ 0.0 0 1

Index Scan using command_queue_idx on command_queue cq1 (cost=0.56..8.58 rows=1 width=23) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: (((status)::text = 'ERROR'::text) AND (to_recalculate = true))
  • Filter: to_recalculate
14. 0.000 0.000 ↓ 0.0 0

Index Scan using command_queue_pkey on command_queue cq (cost=0.56..8.58 rows=1 width=10) (never executed)

  • Index Cond: (id = (min(cq1.id)))
15. 0.009 17,733.014 ↓ 100.0 100 1

Limit (cost=1,122.69..1,122.69 rows=1 width=10) (actual time=17,732.999..17,733.014 rows=100 loops=1)

16. 3.543 17,733.005 ↓ 100.0 100 1

Sort (cost=1,122.69..1,122.69 rows=1 width=10) (actual time=17,732.998..17,733.005 rows=100 loops=1)

  • Sort Key: cq_1.priority DESC, cq_1.id
  • Sort Method: top-N heapsort Memory: 29kB
17. 7.384 17,729.462 ↓ 4,025.0 4,025 1

Nested Loop Anti Join (cost=1,088.96..1,122.68 rows=1 width=10) (actual time=36.675..17,729.462 rows=4,025 loops=1)

18. 6,990.773 17,629.503 ↓ 4,025.0 4,025 1

Nested Loop (cost=1,088.39..1,096.91 rows=1 width=25) (actual time=36.627..17,629.503 rows=4,025 loops=1)

  • Join Filter: (cq_1.id = (min(cq2.id)))
  • Rows Removed by Join Filter: 139001053
19. 30.904 30.904 ↓ 15,762.0 15,762 1

Index Scan using command_queue_status_idx on command_queue cq_1 (cost=0.56..4.59 rows=1 width=25) (actual time=0.023..30.904 rows=15,762 loops=1)

  • Index Cond: ((status)::text = 'PENDING'::text)
20. 10,573.650 10,607.826 ↓ 44.1 8,819 15,762

HashAggregate (cost=1,087.83..1,089.83 rows=200 width=8) (actual time=0.002..0.673 rows=8,819 loops=15,762)

  • Group Key: min(cq2.id)
21. 8.616 34.176 ↓ 2.2 8,819 1

HashAggregate (cost=997.49..1,037.64 rows=4,015 width=23) (actual time=32.658..34.176 rows=8,819 loops=1)

  • Group Key: cq2.target_id
22. 25.560 25.560 ↓ 5.1 20,556 1

Index Scan using command_queue_status_idx on command_queue cq2 (cost=0.56..977.37 rows=4,023 width=23) (actual time=0.022..25.560 rows=20,556 loops=1)

  • Index Cond: ((status)::text = ANY ('{PENDING,ERROR}'::text[]))
23. 92.575 92.575 ↓ 0.0 0 4,025

Index Only Scan using command_queue_idx on command_queue cq3 (cost=0.56..13.17 rows=1 width=15) (actual time=0.023..0.023 rows=0 loops=4,025)

  • Index Cond: ((status = ANY ('{PROCESSING,ERROR}'::text[])) AND (to_recalculate = false) AND (target_id = (cq_1.target_id)::text))
  • Filter: (NOT to_recalculate)
  • Heap Fetches: 1197
Planning time : 1.071 ms
Execution time : 17,733.359 ms