explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L2wq

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.032 ↓ 0.0 0 1

Insert on tmp_queue_event (cost=37.42..38.69 rows=2 width=91) (actual time=0.032..0.032 rows=0 loops=1)

  • ,ctv_upd AS
  • INSERT INTO tmp_queue_event
  • (event_id, created_date, object_id, object_key, object_type_name, action_type_code)
  • SELECT tt.event_id
2.          

CTE ctv

3. 0.001 0.009 ↓ 0.0 0 1

Limit (cost=18.64..18.66 rows=2 width=30) (actual time=0.009..0.009 rows=0 loops=1)

4. 0.000 0.008 ↓ 0.0 0 1

LockRows (cost=18.64..18.66 rows=2 width=30) (actual time=0.008..0.008 rows=0 loops=1)

5. 0.005 0.008 ↓ 0.0 0 1

Sort (cost=18.64..18.64 rows=2 width=30) (actual time=0.008..0.008 rows=0 loops=1)

  • Sort Key: qe.created_date
  • Sort Method: quicksort Memory: 25kB
6. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on queue_event qe (cost=0.00..18.62 rows=2 width=30) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: ((NOT is_failed) AND (status_code = 0))
  • Rows Removed by Filter: 1
7.          

CTE ctv_upd

8. 0.001 0.010 ↓ 0.0 0 1

Update on queue_event qeu (cost=0.15..16.40 rows=2 width=135) (actual time=0.010..0.010 rows=0 loops=1)

9. 0.000 0.009 ↓ 0.0 0 1

Nested Loop (cost=0.15..16.40 rows=2 width=135) (actual time=0.009..0.009 rows=0 loops=1)

10. 0.009 0.009 ↓ 0.0 0 1

CTE Scan on ctv t (cost=0.00..0.04 rows=2 width=56) (actual time=0.009..0.009 rows=0 loops=1)

11. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_queue_event on queue_event qeu (cost=0.15..8.17 rows=1 width=93) (never executed)

  • Index Cond: (event_id = t.event_id)
12. 0.005 0.031 ↓ 0.0 0 1

Hash Join (cost=2.36..3.64 rows=2 width=91) (actual time=0.031..0.031 rows=0 loops=1)

  • Hash Cond: (et.action_type_id = at1.action_type_id)
  • SELECT qe.event_id
  • FROM tvs_sync.queue_event AS qe
  • WHERE 1=1
  • AND qe.is_failed = FALSE
  • AND (p_event_type_id = 0 OR qe.event_type_id = p_event_type_id)
  • AND qe.status_code = 0
  • ORDER BY qe.created_date
  • LIMIT p_row_count
  • FOR UPDATE SKIP LOCKED
  • UPDATE tvs_sync.queue_event AS qeu
  • SET status_code = 1
  • FROM ctv AS t
  • WHERE qeu.event_id = t.event_id
  • RETURNING qeu.event_id
  • ,tt.created_date
  • ,tt.object_id
  • ,tt.object_key
  • ,ot.object_type_name
  • ,at1.action_type_code
  • FROM ctv_upd AS tt
  • JOIN tvs_sync.event_type AS et
  • ON et.event_type_id = tt.event_type_id
  • JOIN tvs_sync.object_type AS ot
  • ON ot.object_type_id = et.object_type_id
  • JOIN tvs_sync.action_type AS at1
  • ON at1.action_type_id = et.action_type_id
13. 0.000 0.017 ↓ 0.0 0 1

Hash Join (cost=1.22..2.50 rows=2 width=86) (actual time=0.017..0.017 rows=0 loops=1)

  • Hash Cond: (et.object_type_id = ot.object_type_id)
14. 0.004 0.017 ↓ 0.0 0 1

Hash Join (cost=0.07..1.33 rows=2 width=76) (actual time=0.017..0.017 rows=0 loops=1)

  • Hash Cond: (et.event_type_id = tt.event_type_id)
15. 0.002 0.002 ↑ 18.0 1 1

Seq Scan on event_type et (cost=0.00..1.18 rows=18 width=6) (actual time=0.002..0.002 rows=1 loops=1)

16. 0.000 0.011 ↓ 0.0 0 1

Hash (cost=0.04..0.04 rows=2 width=74) (actual time=0.011..0.011 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
17. 0.011 0.011 ↓ 0.0 0 1

CTE Scan on ctv_upd tt (cost=0.00..0.04 rows=2 width=74) (actual time=0.011..0.011 rows=0 loops=1)

18. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.07..1.07 rows=7 width=14) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Seq Scan on object_type ot (cost=0.00..1.07 rows=7 width=14) (never executed)

20. 0.009 0.009 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=9) (actual time=0.009..0.009 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Seq Scan on action_type at1 (cost=0.00..1.06 rows=6 width=9) (actual time=0.004..0.005 rows=6 loops=1);
  • ,qeu.created_date
  • ,qeu.object_id
  • ,qeu.object_key
  • ,qeu.event_type_id