explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l7uG

Settings
# exclusive inclusive rows x rows loops node
1. 281.786 3,186.170 ↑ 1.0 25 1

Limit (cost=4,576,796.65..4,576,796.71 rows=25 width=576) (actual time=3,186.166..3,186.170 rows=25 loops=1)

  • Functions: 28
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 2.374 ms, Inlining 8.811 ms, Optimization 159.982 ms, Emission 112.636 ms, Total 283.802 ms
2. 0.044 2,904.384 ↑ 1.3 25 1

Sort (cost=4,576,796.65..4,576,796.73 rows=32 width=576) (actual time=2,904.383..2,904.384 rows=25 loops=1)

  • Sort Key: (max(cm.created)) DESC
  • Sort Method: quicksort Memory: 52kB
3. 0.113 2,904.340 ↓ 1.0 33 1

WindowAgg (cost=0.84..4,576,795.85 rows=32 width=576) (actual time=2,904.326..2,904.340 rows=33 loops=1)

4. 51.866 2,904.227 ↓ 1.0 33 1

Nested Loop (cost=0.84..4,576,795.45 rows=32 width=560) (actual time=49.495..2,904.227 rows=33 loops=1)

  • Join Filter: (chat_group.id = cm.chat_group_id)
  • Rows Removed by Join Filter: 772,539
5. 114.083 222.063 ↑ 1.0 33 1

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

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

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

7. 99.865 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.135 0.135 ↑ 1.0 33 1

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

  • Filter: ((account_id_one = 12) OR (account_id_two = 12))
  • Rows Removed by Filter: 1,936
9. 58.556 2,630.298 ↑ 2.1 23,411 33

Materialize (cost=0.84..4,524,197.84 rows=48,467 width=20) (actual time=0.004..79.706 rows=23,411 loops=33)

10. 122.545 2,571.742 ↓ 1.0 49,671 1

GroupAggregate (cost=0.84..4,523,470.83 rows=48,467 width=20) (actual time=0.129..2,571.742 rows=49,671 loops=1)

  • Group Key: cm.chat_group_id
11. 796.785 2,449.197 ↓ 2.0 988,415 1

Nested Loop Left Join (cost=0.84..4,519,236.16 rows=500,000 width=12) (actual time=0.045..2,449.197 rows=988,415 loops=1)

  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
  • Rows Removed by Filter: 5,606
12. 658.391 658.391 ↑ 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..658.391 rows=994,021 loops=1)

13. 0.000 994.021 ↓ 0.0 0 994,021

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

  • Filter: (cm.chat_group_id = cgr.chat_group_id)
14. 0.000 994.021 ↓ 0.0 0 994,021

Limit (cost=0.41..4.43 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=994,021)

15. 994.021 994.021 ↓ 0.0 0 994,021

Index Only Scan Backward using chat_group_read_main on chat_group_read (cost=0.41..4.43 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=994,021)

  • Index Cond: ((chat_group_id = cm.chat_group_id) AND (account_id = 12))
  • Heap Fetches: 0
Execution time : 3,188.899 ms