How should I optimize the order by index

Question

1.select * from transactions where from_account_id = 51118 order by block_number, index desc limit 20;

 Limit  (cost=295.39..4973.89 rows=20 width=605)
   ->  Incremental Sort  (cost=295.39..2198254.12 rows=9396 width=605)
         Sort Key: block_number, index DESC
         Presorted Key: block_number
         ->  Index Scan using transactions_block_number_index on transactions  (cost=0.44..2197899.34 rows=9396 width=605)
               Filter: (from_account_id = 51118)
(6 rows)

2.select * from transactions where from_account_id = 51118 order by index desc limit 20;

Limit  (cost=35862.79..35862.84 rows=20 width=605)
   ->  Sort  (cost=35862.79..35886.28 rows=9396 width=605)
         Sort Key: index DESC
         ->  Bitmap Heap Scan on transactions  (cost=109.26..35612.76 rows=9396 width=605)
               Recheck Cond: (from_account_id = 51118)
               ->  Bitmap Index Scan on transactions_from_account_id_index  (cost=0.00..106.91 rows=9396 width=0)
                     Index Cond: (from_account_id = 51118)
(7 rows)

The transactions table have index on block_number column and no index on index column. But when order by block_number, it is much more slower. So What can I do? Remove block_nubmer index or add block_number_index index?

---

The tables have 20 million row. And the query result have 30 rows.

---

Query with buffers

 EXPLAIN (ANALYZE,BUFFERS) select * from transactions where from_account_id = 51118 order by block_number, index desc limit 20;

Limit  (cost=292.45..4925.45 rows=20 width=606) (actual time=32347.524..32347.529 rows=20 loops=1)
   Buffers: shared hit=118286 read=721741
   I/O Timings: read=29685.682
   ->  Incremental Sort  (cost=292.45..2195640.47 rows=9477 width=606) (actual time=32347.523..32347.525 rows=20 loops=1)
         Sort Key: block_number, index DESC
         Presorted Key: block_number
         Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 39kB  Peak Memory: 39kB
         Buffers: shared hit=118286 read=721741
         I/O Timings: read=29685.682
         ->  Index Scan using transactions_block_number_index on transactions  (cost=0.44..2195282.57 rows=9477 width=606) (actual time=2399.255..32347.407 rows=22 loops=1)
               Filter: (from_account_id = 51118)
               Rows Removed by Filter: 8398852
               Buffers: shared hit=118277 read=721741
               I/O Timings: read=29685.682
 Planning Time: 0.127 ms
 Execution Time: 32347.557 ms

Answer 1

After filtering you are sorting by two columns, one ascending order and the other one in descending order.

The best index should first allow the correct filtering (this is easy since it's an equality) and then it needs to provide the two columns in the exact ordering you need. If you modify the query as:

select * 
from transactions 
where from_account_id = 51118 
order by block_number, -index
limit 20;

Then, the following index could speed up your query:

create index ix1 on transactions (from_account_id, block_number, (-index));