explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qtaq

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 213,159.792 ↑ 1.0 30 1

Limit (cost=546,965.26..546,965.48 rows=30 width=16) (actual time=213,159.779..213,159.792 rows=30 loops=1)

2. 0.006 213,159.787 ↑ 7.0 30 1

Unique (cost=546,965.26..546,966.83 rows=209 width=16) (actual time=213,159.778..213,159.787 rows=30 loops=1)

3. 14.199 213,159.781 ↑ 7.0 30 1

Sort (cost=546,965.26..546,965.78 rows=209 width=16) (actual time=213,159.777..213,159.781 rows=30 loops=1)

  • Sort Key: statuses.id DESC, statuses.updated_at
  • Sort Method: quicksort Memory: 4415kB
4. 20.350 213,145.582 ↓ 293.9 61,416 1

Nested Loop Left Join (cost=432,485.81..546,957.21 rows=209 width=16) (actual time=196,386.117..213,145.582 rows=61,416 loops=1)

  • Filter: (accounts.silenced_at IS NULL)
  • Rows Removed by Filter: 16
5. 5,380.404 212,940.936 ↓ 280.5 61,432 1

Hash Join (cost=432,485.39..545,512.25 rows=219 width=24) (actual time=196,386.095..212,940.936 rows=61,432 loops=1)

  • Hash Cond: (statuses_tags.status_id = statuses.id)
6. 11,200.943 11,331.483 ↑ 1.0 62,986 1

Bitmap Heap Scan on statuses_tags (cost=2,137.29..114,921.60 rows=64,094 width=8) (actual time=144.683..11,331.483 rows=62,986 loops=1)

  • Recheck Cond: (tag_id = 84)
  • Heap Blocks: exact=42199
7. 130.540 130.540 ↓ 1.1 68,564 1

Bitmap Index Scan on index_statuses_tags_on_tag_id_and_status_id (cost=0.00..2,121.26 rows=64,094 width=0) (actual time=130.540..130.540 rows=68,564 loops=1)

  • Index Cond: (tag_id = 84)
8. 5,587.612 196,229.049 ↓ 193.0 21,535,609 1

Hash (cost=428,953.62..428,953.62 rows=111,558 width=24) (actual time=196,229.049..196,229.049 rows=21,535,609 loops=1)

  • Buckets: 524288 (originally 131072) Batches: 64 (originally 1) Memory Usage: 28673kB
9. 180,896.263 190,641.437 ↓ 193.0 21,535,609 1

Bitmap Heap Scan on statuses (cost=5,265.74..428,953.62 rows=111,558 width=24) (actual time=9,871.449..190,641.437 rows=21,535,609 loops=1)

  • Recheck Cond: ((visibility = 0) AND (deleted_at IS NULL))
  • Rows Removed by Index Recheck: 4863457
  • Filter: (reblog_of_id IS NULL)
  • Rows Removed by Filter: 4961497
  • Heap Blocks: exact=335822 lossy=1691917
10. 9,745.174 9,745.174 ↓ 198.5 26,710,326 1

Bitmap Index Scan on index_statuses_20190820 (cost=0.00..5,237.85 rows=134,537 width=0) (actual time=9,745.174..9,745.174 rows=26,710,326 loops=1)

  • Index Cond: (visibility = 0)
11. 184.296 184.296 ↑ 1.0 1 61,432

Index Scan using index_accounts_on_id on accounts (cost=0.42..6.59 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=61,432)

  • Index Cond: (id = statuses.account_id)
Planning time : 1.905 ms
Execution time : 213,161.440 ms