explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5MZp

Settings
# exclusive inclusive rows x rows loops node
1. 2.765 37,592.833 ↑ 1.0 1 1

Aggregate (cost=1,854,802.85..1,854,802.86 rows=1 width=8) (actual time=37,592.833..37,592.833 rows=1 loops=1)

2. 6.852 37,590.068 ↓ 1.5 14,264 1

Hash Join (cost=11,265.00..1,854,778.90 rows=9,580 width=0) (actual time=1,505.217..37,590.068 rows=14,264 loops=1)

  • Hash Cond: (items.source_id = sources.id)
3. 193.385 37,582.885 ↓ 1.3 14,264 1

Nested Loop (cost=11,248.79..1,854,625.84 rows=10,948 width=4) (actual time=1,504.784..37,582.885 rows=14,264 loops=1)

4. 2,872.610 37,191.293 ↑ 1.3 22,023 1

Hash Join (cost=11,247.23..1,804,510.22 rows=27,531 width=4) (actual time=1,504.436..37,191.293 rows=22,023 loops=1)

  • Hash Cond: ((SubPlan 2) = last_message.id)
5. 3,089.140 3,089.140 ↑ 1.3 2,150,488 1

Seq Scan on tickets (cost=0.00..177,938.95 rows=2,857,246 width=4) (actual time=0.230..3,089.140 rows=2,150,488 loops=1)

  • Filter: (((status)::text = ANY ('{closed,solved_by_pool}'::text[])) AND (NOT ('{Competenza_Tre}'::character varying[] && tags)) AND (((category)::text <> 'CC SOCIAL'::text) OR ((category)::text <> 'IT - EXPERT OFFERTA'::text) OR ((category)::text <> 'IT - EXPERT TECNICO'::text) OR ((category)::text <> 'non categorizzato'::text)))
  • Rows Removed by Filter: 711374
6. 130.087 813.535 ↓ 1.1 116,247 1

Hash (cost=9,940.83..9,940.83 rows=104,512 width=4) (actual time=813.535..813.535 rows=116,247 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5111kB
7. 683.448 683.448 ↓ 1.1 116,247 1

Index Scan using index_items_on_created_at on items last_message (cost=0.43..9,940.83 rows=104,512 width=4) (actual time=2.289..683.448 rows=116,247 loops=1)

  • Index Cond: (created_at >= '2020-01-07 10:30:53.510115'::timestamp without time zone)
8.          

SubPlan (for Hash Join)

9. 0.000 30,416.008 ↑ 1.0 1 2,172,572

Limit (cost=0.43..1.12 rows=1 width=12) (actual time=0.014..0.014 rows=1 loops=2,172,572)

10. 30,416.008 30,416.008 ↑ 13.0 1 2,172,572

Index Scan Backward using index_items_on_ticket_id_and_created_at on items items_2 (cost=0.43..9.38 rows=13 width=12) (actual time=0.014..0.014 rows=1 loops=2,172,572)

  • Index Cond: (ticket_id = tickets.id)
  • Filter: ((type)::text = 'Message'::text)
  • Rows Removed by Filter: 0
11. 22.023 198.207 ↑ 1.0 1 22,023

Index Scan using items_pkey on items (cost=1.56..1.81 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=22,023)

  • Index Cond: (id = (SubPlan 1))
  • Filter: (source_id = ANY ('{21,32,17,33,18,31,34,20,36,35,19}'::integer[]))
  • Rows Removed by Filter: 0
12.          

SubPlan (for Index Scan)

13. 0.000 176.184 ↑ 1.0 1 22,023

Limit (cost=0.43..1.12 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=22,023)

14. 176.184 176.184 ↑ 13.0 1 22,023

Index Scan using index_items_on_ticket_id_and_created_at on items items_1 (cost=0.43..9.38 rows=13 width=12) (actual time=0.008..0.008 rows=1 loops=22,023)

  • Index Cond: (ticket_id = tickets.id)
  • Filter: ((type)::text = 'Message'::text)
15. 0.091 0.331 ↑ 1.0 58 1

Hash (cost=15.48..15.48 rows=58 width=4) (actual time=0.331..0.331 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
16. 0.240 0.240 ↑ 1.0 58 1

Index Only Scan using index_sources_on_id_and_workspace_id on sources (cost=0.27..15.48 rows=58 width=4) (actual time=0.033..0.240 rows=58 loops=1)

  • Heap Fetches: 17
Planning time : 31.430 ms
Execution time : 37,596.789 ms