explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8uiJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.033 17,268.657 ↑ 1.0 1 1

Aggregate (cost=4,155,912.89..4,155,912.90 rows=1 width=8) (actual time=17,268.657..17,268.657 rows=1 loops=1)

2. 0.041 17,268.624 ↓ 7.2 297 1

Nested Loop (cost=133.10..4,155,912.79 rows=41 width=0) (actual time=973.614..17,268.624 rows=297 loops=1)

3. 0.030 0.030 ↑ 1.0 1 1

Index Only Scan using index_sources_on_id_and_source_type_code on sources (cost=0.14..8.16 rows=1 width=4) (actual time=0.029..0.030 rows=1 loops=1)

  • Index Cond: (id = 17)
  • Heap Fetches: 1
4. 1.804 17,268.553 ↓ 7.2 297 1

Nested Loop (cost=132.96..4,155,904.22 rows=41 width=4) (actual time=973.582..17,268.553 rows=297 loops=1)

5. 1,324.714 17,265.117 ↓ 3.6 544 1

Hash Join (cost=129.35..4,154,725.26 rows=152 width=4) (actual time=973.559..17,265.117 rows=544 loops=1)

  • Hash Cond: ((SubPlan 2) = last_message.id)
6. 745.561 745.561 ↑ 1.0 2,531,627 1

Seq Scan on tickets (cost=0.00..113,442.02 rows=2,539,502 width=4) (actual time=0.016..745.561 rows=2,531,627 loops=1)

7. 0.299 1.810 ↓ 2.5 1,432 1

Hash (cost=122.26..122.26 rows=567 width=4) (actual time=1.810..1.810 rows=1,432 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 67kB
8. 1.511 1.511 ↓ 2.5 1,432 1

Index Scan using index_items_on_created_at on items last_message (cost=0.43..122.26 rows=567 width=4) (actual time=0.024..1.511 rows=1,432 loops=1)

  • Index Cond: (created_at >= '2019-05-10 06:59:33.998338'::timestamp without time zone)
9.          

SubPlan (forHash Join)

10. 0.000 15,193.032 ↑ 1.0 1 2,532,172

Limit (cost=0.43..3.18 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=2,532,172)

11. 15,193.032 15,193.032 ↑ 11.0 1 2,532,172

Index Scan Backward using index_items_on_ticket_id_and_created_at on items items_2 (cost=0.43..30.58 rows=11 width=12) (actual time=0.006..0.006 rows=1 loops=2,532,172)

  • Index Cond: (ticket_id = tickets.id)
  • Filter: ((type)::text = 'Message'::text)
  • Rows Removed by Filter: 0
12. 0.544 1.632 ↑ 1.0 1 544

Index Scan using items_pkey on items (cost=3.61..7.75 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=544)

  • Index Cond: (id = (SubPlan 1))
  • Filter: (source_id = 17)
  • Rows Removed by Filter: 0
13.          

SubPlan (forIndex Scan)

14. 0.000 1.088 ↑ 1.0 1 544

Limit (cost=0.43..3.18 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=544)

15. 1.088 1.088 ↑ 11.0 1 544

Index Scan using index_items_on_ticket_id_and_created_at on items items_1 (cost=0.43..30.58 rows=11 width=12) (actual time=0.002..0.002 rows=1 loops=544)

  • Index Cond: (ticket_id = tickets.id)
  • Filter: ((type)::text = 'Message'::text)
Planning time : 3.360 ms
Execution time : 17,268.818 ms