explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YCQq

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.123 ↑ 1.0 1 1

Unique (cost=43.99..44.06 rows=1 width=250) (actual time=0.112..0.123 rows=1 loops=1)

2.          

Initplan (for Unique)

3. 0.001 0.019 ↑ 1.0 1 1

Aggregate (cost=23.16..23.17 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=1)

4. 0.000 0.018 ↓ 0.0 0 1

Nested Loop (cost=4.13..23.16 rows=1 width=117) (actual time=0.018..0.018 rows=0 loops=1)

  • Join Filter: (message_assignment_transitions_1.message_id = messages_1.id)
5. 0.007 0.007 ↑ 1.0 6 1

Seq Scan on messages messages_1 (cost=0.00..1.21 rows=6 width=4) (actual time=0.004..0.007 rows=6 loops=1)

  • Filter: ((hotel_id = 1) AND ((resolution_state)::text = 'unresolved'::text))
  • Rows Removed by Filter: 8
6. 0.008 0.012 ↓ 0.0 0 6

Materialize (cost=4.13..21.68 rows=3 width=121) (actual time=0.002..0.002 rows=0 loops=6)

7. 0.001 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on message_assignment_transitions message_assignment_transitions_1 (cost=4.13..21.67 rows=3 width=121) (actual time=0.004..0.004 rows=0 loops=1)

  • Recheck Cond: most_recent
  • Filter: (((to_state)::text = ANY ('{assigned,accepted}'::text[])) AND ((metadata ->> 'assigned_to'::text) <> '3'::text) AND ((metadata ->> 'accepted_by'::text) <> '3'::text))
8. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on index_message_assignment_transitions_parent_most_recent (cost=0.00..4.13 rows=335 width=0) (actual time=0.003..0.003 rows=0 loops=1)

9. 0.040 0.112 ↓ 6.0 6 1

Sort (cost=20.82..20.82 rows=1 width=250) (actual time=0.112..0.112 rows=6 loops=1)

  • Sort Key: visits.id, visits.hotel_id, visits.guest_id, visits.user_id, visits.checked_in_at, visits.checked_out_at, visits.removed_at, visits.created_at, visits.updated_at, visits.agent_name, visits.room_number, visits.checked_in, visits.check_out_worker_jid, visits.active_message_count, visits.unverified, visits.check_in_worker_jid, visits.messages_count, visits.delta, visits.received_message_count, visits.status, visits.guest_rating, visits.custom_variable, visits.external_id, visits.custom_variables, visits.last_message_type, visits.unassigned_and_unresolved_message_count, visits.opted_in_at
  • Sort Method: quicksort Memory: 26kB
10. 0.007 0.072 ↓ 6.0 6 1

Nested Loop (cost=1.25..20.81 rows=1 width=250) (actual time=0.059..0.072 rows=6 loops=1)

  • Join Filter: (messages.visit_id = visits.id)
11. 0.014 0.035 ↓ 6.0 6 1

Hash Right Join (cost=1.25..19.78 rows=1 width=4) (actual time=0.031..0.035 rows=6 loops=1)

  • Hash Cond: (message_assignment_transitions.message_id = messages.id)
  • Filter: ((message_assignment_transitions.most_recent OR (message_assignment_transitions.most_recent IS NULL)) AND (((message_assignment_transitions.to_state)::text = ANY ('{assigned,accepted}'::text[])) OR (message_assignment_transitions.to_state IS NULL)) AND ((message_assignment_transitions.metadata IS NULL) OR ((message_assignment_transitions.metadata ->> 'assigned_to'::text) = '3'::text) OR ((message_assignment_transitions.metadata ->> 'accepted_by'::text) = '3'::text)))
12. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on message_assignment_transitions (cost=0.00..16.70 rows=670 width=69) (actual time=0.002..0.002 rows=0 loops=1)

13. 0.004 0.019 ↑ 1.0 6 1

Hash (cost=1.18..1.18 rows=6 width=8) (actual time=0.019..0.019 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.015 0.015 ↑ 1.0 6 1

Seq Scan on messages (cost=0.00..1.18 rows=6 width=8) (actual time=0.011..0.015 rows=6 loops=1)

  • Filter: ((resolution_state)::text = 'unresolved'::text)
  • Rows Removed by Filter: 8
15. 0.030 0.030 ↑ 1.0 1 6

Seq Scan on visits (cost=0.00..1.01 rows=1 width=250) (actual time=0.005..0.005 rows=1 loops=6)

  • Filter: (((status)::text <> 'invalid'::text) AND (active_message_count > $0))