How to index range condition and ordering by a different colum? I use PostgreSQL and i have a table like this:
create table transactions (
  uuid uuid,
  user_id integer,
  created_at timestamp,
  amount numeric
);
create index idx_transactions_user_id_created_at on transactions (user_id, created_at);

I'd like to get the user's &quot;biggest transactions&quot; for the past N days with a query like this:
select *
from transactions
where user_id=1234
  and created_at &gt;= current_timestamp - interval '7 days'
order by amount desc
limit 10;

The user can choose the number of days and the limit he is interested in.
The query plan is like this:
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Limit  (cost=9.54..9.55 rows=2 width=60)
   -&gt;  Sort  (cost=9.54..9.55 rows=2 width=60)
         Sort Key: amount DESC
         -&gt;  Bitmap Heap Scan on transactions  (cost=4.18..9.53 rows=2 width=60)
               Recheck Cond: ((user_id = 1234) AND (created_at &gt;= (CURRENT_TIMESTAMP - '7 days'::interval)))
               -&gt;  Bitmap Index Scan on idx_transactions_user_id_created_at  (cost=0.00..4.17 rows=2 width=0)
                     Index Cond: ((user_id = 1234) AND (created_at &gt;= (CURRENT_TIMESTAMP - '7 days'::interval)))
(7 rows)

So postgres gets all rows which match the user_id and the created_at range, than sorts all that and returns the top N.
Is there's a better way to index this? So it scales nicely when there are millions of users and millions of transactions per user?
So far my query is not too slow, but i just wonder if it is theoretically possible to index this table in such a way that postgres will know exactly which 10 row to read from the disk for arbitrary created_at condition?
No, you can't do that with any of the core index types.  You might be able to use the RUM extension, but last time I tried something like this with it I couldn't get it to work for this purpose.
Once you have enough data that you actually need improvement (sorting two rows is not laborious, as you note), you might get a big improvement by range partitioning on created_at, and indexing on (user_id, amount).  That way it could consult only the partitions covering possibly-qualifying times, walking each partition in order by the index, and filtering out any rows that didn't actually qualify based on created_at but which was in a partition which couldn't be ruled out en masse.  Each individual partition scan can stop once it meets the LIMIT, and then they can be &quot;merge appended&quot; maintaining the existing order, and have the LIMIT applied again to the merged set.
After playing with this some more, I have to amend my answer.  A GiST index on (user_id, created_at, amount) can do this as long as you can reformulate your query a bit.  You can use the KNN mechanism by comparing &quot;amount&quot; to a 'sentinel' value which is greater than any possible real amount in your table:
select *
from transactions
where user_id=1234
  and created_at &gt;= current_timestamp - interval '7 days'
order by amount &lt;-&gt; 999999999
limit 10;

You do need a real max amount here, you can't use 'Infinity' because the distance between any finite value and infinity is infinite, and they will all be tied.
GiST indexes can be much slower to build and maintain than btree indexes, and that can also be hard to predict.  So even though it is possible, it might not be worthwhile if other techniques with btree indexes will get the job done.