explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y58J

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 292,623.419 ↓ 0.0 0 1

Limit (cost=7,814,826.56..7,814,826.76 rows=10 width=240) (actual time=292,623.419..292,623.419 rows=0 loops=1)

2.          

CTE vars

3. 0.001 0.001 ↑ 1.0 1 1

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

4.          

CTE job_cursor

5. 0.013 0.050 ↑ 1.0 1 1

Hash Join (cost=0.03..1.17 rows=1 width=73) (actual time=0.049..0.050 rows=1 loops=1)

  • Hash Cond: (batch_cursor.job = vars.job_name)
6. 0.023 0.023 ↑ 1.0 9 1

Seq Scan on batch_cursor (cost=0.00..1.09 rows=9 width=37) (actual time=0.022..0.023 rows=9 loops=1)

7. 0.010 0.014 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=36) (actual time=0.014..0.014 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on vars (cost=0.00..0.02 rows=1 width=36) (actual time=0.003..0.004 rows=1 loops=1)

9.          

CTE recent_transfer_event

10. 0.001 0.100 ↓ 0.0 0 1

GroupAggregate (cost=527,151.79..546,350.74 rows=45,180 width=64) (actual time=0.100..0.100 rows=0 loops=1)

  • Group Key: transfer_event.tenant_id, transfer_event.content_id
11. 0.005 0.099 ↓ 0.0 0 1

Sort (cost=527,151.79..531,838.58 rows=1,874,715 width=64) (actual time=0.099..0.099 rows=0 loops=1)

  • Sort Key: transfer_event.tenant_id, transfer_event.content_id
  • Sort Method: quicksort Memory: 25kB
12. 0.003 0.094 ↓ 0.0 0 1

Nested Loop (cost=0.44..190,849.98 rows=1,874,715 width=64) (actual time=0.094..0.094 rows=0 loops=1)

13. 0.052 0.052 ↑ 1.0 1 1

CTE Scan on job_cursor (cost=0.00..0.02 rows=1 width=8) (actual time=0.051..0.052 rows=1 loops=1)

14. 0.039 0.039 ↓ 0.0 0 1

Index Scan using transfer_event_pkey on transfer_event (cost=0.44..172,102.81 rows=1,874,715 width=64) (actual time=0.038..0.039 rows=0 loops=1)

  • Index Cond: ((id > job_cursor.transfer_event_id) AND (id <= (job_cursor.transfer_event_id + job_cursor.max_records)))
15.          

CTE all_transfer_events

16. 0.003 292,623.416 ↓ 0.0 0 1

Merge Join (cost=7,134,338.77..7,268,474.65 rows=337,449 width=161) (actual time=292,623.416..292,623.416 rows=0 loops=1)

  • Merge Cond: ((b.tenant_id = a.tenant_id) AND (b.content_id = a.content_id))
17. 253,075.085 292,623.271 ↑ 16,872,436.0 1 1

Sort (cost=7,129,941.99..7,172,123.08 rows=16,872,436 width=154) (actual time=292,623.270..292,623.271 rows=1 loops=1)

  • Sort Key: b.tenant_id, b.content_id
  • Sort Method: external merge Disk: 2559104kB
18. 39,548.186 39,548.186 ↑ 1.0 16,621,612 1

Seq Scan on transfer_event b (cost=0.00..1,125,365.36 rows=16,872,436 width=154) (actual time=0.064..39,548.186 rows=16,621,612 loops=1)

19. 0.040 0.142 ↓ 0.0 0 1

Sort (cost=4,396.78..4,509.73 rows=45,180 width=64) (actual time=0.142..0.142 rows=0 loops=1)

  • Sort Key: a.tenant_id, a.content_id
  • Sort Method: quicksort Memory: 25kB
20. 0.102 0.102 ↓ 0.0 0 1

CTE Scan on recent_transfer_event a (cost=0.00..903.60 rows=45,180 width=64) (actual time=0.102..0.102 rows=0 loops=1)

21. 292,623.419 292,623.419 ↓ 0.0 0 1

CTE Scan on all_transfer_events (cost=0.00..6,748.98 rows=337,449 width=240) (actual time=292,623.418..292,623.419 rows=0 loops=1)

Planning time : 18.126 ms
Execution time : 317,021.301 ms