explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jjFX

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 242,454.085 ↑ 1.0 30 1

Limit (cost=588,681.19..588,681.42 rows=30 width=16) (actual time=242,454.071..242,454.085 rows=30 loops=1)

2. 0.005 242,454.079 ↑ 27.1 30 1

Unique (cost=588,681.19..588,687.29 rows=813 width=16) (actual time=242,454.070..242,454.079 rows=30 loops=1)

3. 32.042 242,454.074 ↑ 27.1 30 1

Sort (cost=588,681.19..588,683.23 rows=813 width=16) (actual time=242,454.068..242,454.074 rows=30 loops=1)

  • Sort Key: statuses.id DESC, statuses.updated_at
  • Sort Method: quicksort Memory: 8660kB
4. 51.224 242,422.032 ↓ 146.6 119,193 1

Nested Loop Left Join (cost=438,625.96..588,641.90 rows=813 width=16) (actual time=205,085.570..242,422.032 rows=119,193 loops=1)

  • Filter: (accounts.silenced_at IS NULL)
  • Rows Removed by Filter: 88266
5. 5,652.229 241,955.890 ↓ 243.8 207,459 1

Hash Join (cost=438,625.54..583,027.05 rows=851 width=24) (actual time=205,085.533..241,955.890 rows=207,459 loops=1)

  • Hash Cond: (statuses_tags.status_id = statuses.id)
6. 31,242.785 31,710.033 ↓ 1.0 252,280 1

Bitmap Heap Scan on statuses_tags (cost=8,303.58..151,762.22 rows=249,164 width=8) (actual time=485.828..31,710.033 rows=252,280 loops=1)

  • Recheck Cond: (tag_id = 433)
  • Heap Blocks: exact=81838
7. 467.248 467.248 ↓ 1.1 272,755 1

Bitmap Index Scan on index_statuses_tags_on_tag_id_and_status_id (cost=0.00..8,241.29 rows=249,164 width=0) (actual time=467.248..467.248 rows=272,755 loops=1)

  • Index Cond: (tag_id = 433)
8. 5,614.808 204,593.628 ↓ 193.0 21,534,411 1

Hash (cost=428,927.57..428,927.57 rows=111,551 width=24) (actual time=204,593.628..204,593.628 rows=21,534,411 loops=1)

  • Buckets: 524288 (originally 131072) Batches: 64 (originally 1) Memory Usage: 28673kB
9. 188,750.255 198,978.820 ↓ 193.0 21,534,411 1

Bitmap Heap Scan on statuses (cost=5,265.67..428,927.57 rows=111,551 width=24) (actual time=10,318.292..198,978.820 rows=21,534,411 loops=1)

  • Recheck Cond: ((visibility = 0) AND (deleted_at IS NULL))
  • Rows Removed by Index Recheck: 4863484
  • Filter: (reblog_of_id IS NULL)
  • Rows Removed by Filter: 4961081
  • Heap Blocks: exact=335699 lossy=1691917
10. 10,228.565 10,228.565 ↓ 198.5 26,708,584 1

Bitmap Index Scan on index_statuses_20190820 (cost=0.00..5,237.78 rows=134,529 width=0) (actual time=10,228.565..10,228.565 rows=26,708,584 loops=1)

  • Index Cond: (visibility = 0)
11. 414.918 414.918 ↑ 1.0 1 207,459

Index Scan using index_accounts_on_id on accounts (cost=0.42..6.59 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=207,459)

  • Index Cond: (id = statuses.account_id)
Planning time : 4.075 ms
Execution time : 242,456.291 ms