explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Skd

Settings
# exclusive inclusive rows x rows loops node
1. 335.588 5,089.524 ↑ 1.0 25 1

Limit (cost=3,553,181.61..3,553,181.67 rows=25 width=576) (actual time=5,089.520..5,089.524 rows=25 loops=1)

  • Functions: 30
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 2.431 ms, Inlining 51.052 ms, Optimization 161.742 ms, Emission 122.387 ms, Total 337.612 ms
2. 0.027 4,753.936 ↑ 1.3 25 1

Sort (cost=3,553,181.61..3,553,181.69 rows=32 width=576) (actual time=4,753.934..4,753.936 rows=25 loops=1)

  • Sort Key: (max(cm.created)) DESC
  • Sort Method: quicksort Memory: 52kB
3. 0.118 4,753.909 ↓ 1.0 33 1

WindowAgg (cost=0.84..3,553,180.81 rows=32 width=576) (actual time=4,753.895..4,753.909 rows=33 loops=1)

4. 57.492 4,753.791 ↓ 1.0 33 1

Nested Loop (cost=0.84..3,553,180.41 rows=32 width=560) (actual time=71.470..4,753.791 rows=33 loops=1)

  • Join Filter: (chat_group.id = cm.chat_group_id)
  • Rows Removed by Join Filter: 772,936
5. 115.551 226.944 ↑ 1.0 33 1

Nested Loop (cost=0.00..28,727.62 rows=33 width=540) (actual time=3.903..226.944 rows=33 loops=1)

  • Join Filter: (chat_group.id = direct_chat_group.chat_group_id)
  • Rows Removed by Join Filter: 1,649,967
6. 11.393 11.393 ↑ 1.0 50,000 1

Seq Scan on chat_group (cost=0.00..3,922.00 rows=50,000 width=516) (actual time=0.010..11.393 rows=50,000 loops=1)

7. 99.903 100.000 ↑ 1.0 33 50,000

Materialize (cost=0.00..55.70 rows=33 width=24) (actual time=0.000..0.002 rows=33 loops=50,000)

8. 0.097 0.097 ↑ 1.0 33 1

Seq Scan on direct_chat_group (cost=0.00..55.53 rows=33 width=24) (actual time=0.012..0.097 rows=33 loops=1)

  • Filter: ((account_id_one = 12) OR (account_id_two = 12))
  • Rows Removed by Filter: 1,936
9. 79.899 4,469.355 ↑ 2.1 23,423 33

Materialize (cost=0.84..3,500,582.79 rows=48,467 width=20) (actual time=0.004..135.435 rows=23,423 loops=33)

10. 128.538 4,389.456 ↓ 1.0 49,695 1

GroupAggregate (cost=0.84..3,499,855.79 rows=48,467 width=20) (actual time=0.136..4,389.456 rows=49,695 loops=1)

  • Group Key: cm.chat_group_id
11. 585.311 4,260.918 ↓ 2.0 994,014 1

Nested Loop Left Join (cost=0.84..3,495,621.12 rows=500,000 width=12) (actual time=0.023..4,260.918 rows=994,014 loops=1)

  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
  • Rows Removed by Filter: 7
12. 693.544 693.544 ↑ 1.0 994,021 1

Index Scan using chat_message_chat_group_id on chat_message cm (cost=0.42..59,236.16 rows=1,000,000 width=12) (actual time=0.009..693.544 rows=994,021 loops=1)

13. 0.000 2,982.063 ↓ 0.0 0 994,021

Subquery Scan on cgr (cost=0.41..3.42 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=994,021)

  • Filter: (cm.chat_group_id = cgr.chat_group_id)
  • Rows Removed by Filter: 1
14. 0.000 2,982.063 ↑ 1.0 1 994,021

Limit (cost=0.41..3.41 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=994,021)

15. 994.021 2,982.063 ↑ 478.0 1 994,021

Result (cost=0.41..1,432.69 rows=478 width=12) (actual time=0.003..0.003 rows=1 loops=994,021)

  • One-Time Filter: (cm.chat_group_id = cm.chat_group_id)
16. 1,988.042 1,988.042 ↑ 478.0 1 994,021

Index Scan using chat_group_read_order_idx on chat_group_read (cost=0.41..1,432.69 rows=478 width=12) (actual time=0.002..0.002 rows=1 loops=994,021)

  • Index Cond: (account_id = 12)
Execution time : 5,092.316 ms