explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7LXp : Horrible query

Settings
# exclusive inclusive rows x rows loops node
1. 2.440 176,194.787 ↓ 2.4 678 1

Subquery Scan on a (cost=27,483,545.25..27,562,336.28 rows=285 width=48) (actual time=783.628..176,194.787 rows=678 loops=1)

2. 0.691 610.687 ↓ 2.4 678 1

Sort (cost=27,483,545.25..27,483,545.97 rows=285 width=16) (actual time=610.099..610.687 rows=678 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 56kB
3. 1.087 609.996 ↓ 2.4 678 1

GroupAggregate (cost=27,483,525.50..27,483,533.63 rows=285 width=16) (actual time=608.255..609.996 rows=678 loops=1)

  • Group Key: message.fwd_channel_id
4. 3.686 608.909 ↓ 21.5 15,143 1

Sort (cost=27,483,525.50..27,483,527.26 rows=705 width=8) (actual time=608.249..608.909 rows=15,143 loops=1)

  • Sort Key: message.fwd_channel_id
  • Sort Method: quicksort Memory: 1094kB
5. 470.011 605.223 ↓ 21.5 15,143 1

Seq Scan on message (cost=0.00..27,483,492.14 rows=705 width=8) (actual time=4.281..605.223 rows=15,143 loops=1)

  • Filter: ((fwd_channel_id IS NOT NULL) AND ((SubPlan 2) IS NULL))
  • Rows Removed by Filter: 3280188
6.          

SubPlan (forSeq Scan)

7. 135.212 135.212 ↑ 1.0 1 135,212

Index Scan using channel_tg_id_idx on channel channel_1 (cost=0.28..8.29 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=135,212)

  • Index Cond: (tg_id = message.fwd_channel_id)
8.          

SubPlan (forSubquery Scan)

9. 2.034 175,581.660 ↑ 1.0 1 678

Limit (cost=0.28..276.45 rows=1 width=32) (actual time=258.968..258.970 rows=1 loops=678)

10. 7.860 175,579.626 ↑ 414.0 1 678

Nested Loop (cost=0.28..114,335.59 rows=414 width=32) (actual time=258.967..258.967 rows=1 loops=678)

11. 175,564.710 175,564.710 ↑ 441.0 1 678

Seq Scan on message message_1 (cost=0.00..114,073.73 rows=441 width=16) (actual time=255.127..258.945 rows=1 loops=678)

  • Filter: (fwd_channel_id = a.fwd_channel_id)
  • Rows Removed by Filter: 2007593
12. 7.056 7.056 ↑ 1.0 1 882

Index Scan using channel_tg_id_idx on channel (cost=0.28..0.58 rows=1 width=21) (actual time=0.008..0.008 rows=1 loops=882)

  • Index Cond: (tg_id = message_1.channel_id)
  • Filter: (username IS NOT NULL)
  • Rows Removed by Filter: 0