explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hGAI : old

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 3,763.993 ↑ 1.0 40 1

Limit (cost=623,953.09..623,953.19 rows=40 width=830) (actual time=3,763.965..3,763.993 rows=40 loops=1)

  • Buffers: shared hit=233122, temp read=23678 written=23581
2. 21.942 3,763.976 ↑ 1,163.1 40 1

Sort (cost=623,953.09..624,069.4 rows=46,524 width=830) (actual time=3,763.963..3,763.976 rows=40 loops=1)

  • Sort Key: m.created DESC
  • Sort Method: top-N heapsort Memory: 76kB
  • Buffers: shared hit=233122, temp read=23678 written=23581
3. 64.633 3,742.034 ↑ 1.1 41,514 1

Unique (cost=614,108.16..622,017.24 rows=46,524 width=830) (actual time=3,405.678..3,742.034 rows=41,514 loops=1)

  • Buffers: shared hit=233119, temp read=23678 written=23581
4. 886.673 3,677.401 ↑ 1.1 41,514 1

Sort (cost=614,108.16..614,224.47 rows=46,524 width=830) (actual time=3,405.676..3,677.401 rows=41,514 loops=1)

  • Sort Key: c.conversation_id, c.first_message_location, c.last_message_location, c.conversation_alias, c.app_id, c.user_id, c.status, c.created, c.updated, c.reopened, c.assigned_at, c.user_source, c.is_default, c.offline_conv, c.csat_message_id, m.message_id, m.message_fragments, m.message_type, m.message_user_id, m.app_id, m.conversation_id, m.created, m.alias, m.rule_id, m.internal_meta, m.status_fragment, ku.user_alias, ku.user_type, ku.first_name, ku.last_name, ku.email, ku.created, ku.updated, ku.notification_type, ku.notification_id, ku.user_provided_identifier, ku.auth_status, ku.auth_token, ku.client_type, ku.profile_pic_url, m.read_by_user, m.source, m.delivered_at, c.assigned_to, c.deleted, (NULL::bigint), c.assigned_to_group, c.channel_id, m.label_id, m.label_category_id, c.label_id, c.category_id, c.user_read_until, cr.created, cr.issue_resolved, cr.response, cr.stars, cs.survey_question, c.reopened, c.responded, c.response_due_type, c.handled_bot_id, c.follow_up, c.force_assignee, c.user_hard_deleted, c.do_not_auto_resolve, c.user_auth_status
  • Sort Method: external merge Disk: 29832kB
  • Buffers: shared hit=233119, temp read=23678 written=23581
5. 18.282 2,790.728 ↑ 1.1 41,514 1

Append (cost=120,956.7..593,484.23 rows=46,524 width=830) (actual time=2,217.076..2,790.728 rows=41,514 loops=1)

  • Buffers: shared hit=233097, temp read=19937 written=19840
6. 28.603 2,772.426 ↑ 1.1 41,514 1

Hash Join (cost=120,956.7..592,991.42 rows=46,523 width=2,380) (actual time=2,217.075..2,772.426 rows=41,514 loops=1)

  • Buffers: shared hit=233090, temp read=19937 written=19840
7. 55.309 2,743.664 ↑ 1.1 41,514 1

Nested Loop (cost=120,947.82..592,342.85 rows=46,523 width=2,338) (actual time=2,216.895..2,743.664 rows=41,514 loops=1)

  • Buffers: shared hit=233086, temp read=19937 written=19840
8. 23.293 2,605.327 ↑ 1.1 41,514 1

Nested Loop (cost=120,947.38..218,533.96 rows=46,523 width=1,048) (actual time=2,216.874..2,605.327 rows=41,514 loops=1)

  • Buffers: shared hit=66184, temp read=19937 written=19840
9. 0.007 0.048 ↑ 1.0 1 1

Nested Loop (cost=0.86..16.91 rows=1 width=435) (actual time=0.044..0.048 rows=1 loops=1)

  • Buffers: shared hit=8
10. 0.021 0.021 ↑ 1.0 1 1

Index Scan using conversation_pkey on conversation c (cost=0.43..8.45 rows=1 width=435) (actual time=0.02..0.021 rows=1 loops=1)

  • Index Cond: (c.conversation_id = '137134998004934'::bigint)
  • Buffers: shared hit=4
11. 0.020 0.020 ↑ 1.0 1 1

Index Scan using conversation_participant_conversation_id_idx on conversation_participant cp (cost=0.43..8.45 rows=1 width=8) (actual time=0.019..0.02 rows=1 loops=1)

  • Index Cond: (cp.conversation_id = '137134998004934'::bigint)
  • Buffers: shared hit=4
12. 299.009 2,581.986 ↑ 1.1 41,514 1

Hash Join (cost=120,946.52..218,051.82 rows=46,523 width=613) (actual time=2,216.827..2,581.986 rows=41,514 loops=1)

  • Buffers: shared hit=66176, temp read=19937 written=19840
13. 66.412 66.412 ↑ 1.1 41,514 1

Index Scan using message_conversation_id_idx on message m (cost=0.57..60,788.48 rows=46,523 width=572) (actual time=0.021..66.412 rows=41,514 loops=1)

  • Index Cond: (m.conversation_id = '137134998004934'::bigint)
  • Filter: (m.created < '2019-09-06 08:03:39.71'::timestamp without time zone)
  • Buffers: shared hit=35611
14. 1,206.823 2,216.565 ↓ 1.1 2,898,301 1

Hash (cost=57,753.42..57,753.42 rows=2,718,842 width=57) (actual time=2,216.565..2,216.565 rows=2,898,301 loops=1)

  • Buffers: shared hit=30565, temp written=19587
15. 1,009.742 1,009.742 ↓ 1.1 2,898,301 1

Seq Scan on csat_response cr (cost=0..57,753.42 rows=2,718,842 width=57) (actual time=0.004..1,009.742 rows=2,898,301 loops=1)

  • Buffers: shared hit=30565
16. 83.028 83.028 ↑ 1.0 1 41,514

Index Scan using user_pkey on konotor_user ku (cost=0.43..8.02 rows=1 width=1,298) (actual time=0.002..0.002 rows=1 loops=41,514)

  • Index Cond: (ku.user_id = m.message_user_id)
  • Buffers: shared hit=166902
17. 0.084 0.159 ↓ 1.0 227 1

Hash (cost=6.17..6.17 rows=217 width=42) (actual time=0.159..0.159 rows=227 loops=1)

  • Buffers: shared hit=4
18. 0.075 0.075 ↓ 1.0 227 1

Seq Scan on csat cs (cost=0..6.17 rows=217 width=42) (actual time=0.008..0.075 rows=227 loops=1)

  • Buffers: shared hit=4
19. 0.000 0.020 ↓ 0.0 0 1

Nested Loop (cost=1.43..27.56 rows=1 width=1,331) (actual time=0.02..0.02 rows=0 loops=1)

  • Buffers: shared hit=7
20. 0.001 0.020 ↓ 0.0 0 1

Nested Loop (cost=1..22.04 rows=1 width=790) (actual time=0.02..0.02 rows=0 loops=1)

  • Buffers: shared hit=7
21. 0.001 0.019 ↓ 0.0 0 1

Nested Loop (cost=0.85..21.85 rows=1 width=425) (actual time=0.019..0.019 rows=0 loops=1)

  • Buffers: shared hit=7
22. 0.012 0.012 ↑ 1.0 1 1

Index Scan using conversation_pkey on conversation c_1 (cost=0.43..8.45 rows=1 width=417) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: (c_1.conversation_id = '137134998004934'::bigint)
  • Buffers: shared hit=4
23. 0.006 0.006 ↓ 0.0 0 1

Index Scan using marketing_user_user_id_idx on marketing_user mu (cost=0.42..13.37 rows=3 width=16) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (mu.user_id = c_1.user_id)
  • Buffers: shared hit=3
24. 0.000 0.000 ↓ 0.0 0 0

Index Scan using marketing_pkey on marketing ma (cost=0.14..0.17 rows=1 width=389) (never executed)

  • Index Cond: (ma.marketing_id = mu.marketing_id)
  • Filter: ((ma.created > '1969-12-31 23:59:59.999'::timestamp without time zone) AND (ma.created < '2019-09-06 08:03:39.71'::timestamp without time zone))
25. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using user_pkey on konotor_user ku_1 (cost=0.43..5.52 rows=1 width=8) (never executed)

  • Index Cond: (ku_1.user_id = ma.started_by_user_id)