It can use one index to provide the filter, and then do the sort. Or it can use the other to provide the ORDER BY, and then stop early based on the LIMIT. It has to choose, as it can't do both. PostgreSQL has no way of knowing that everything with "big_table"."user_id" = 13 AND "big_table"."action" = 1 were also created a long time ago, so it doesn't know that the that stopping early based on the LIMIT will not actually top very early.

It is hard to figure out what your question is. You seem to know what the answer is, build the index on (user_id, action, created_at). So do that, if you want the performance problem solved.

You say you are confused, and also that you can't change the query. Being able to change the query coming from your app is irrelevant to resolving your confusion. Even if unfortunate tooling limitations prevent you from implementing a solution, that doesn't prevent you from understanding either the solution or the problem.

Are you seeking understanding, or a solution?

Both my local and the remote tables have the same number of entries (~25mio.) and about the same data distribution

There are many dimensions of data distribution. Maybe they are similar in some, but not in others. Seeing the output of EXPLAIN (ANALYZE, BUFFERS) for both servers could really help, but likely would not be enough. It would also be nice to see EXPLAIN (ANALYZE, BUFFERS) from the slow server when it is using the fast plan. You can do this by dropping the wrong index, or by changing the query so it uses ORDER BY (big_table.created_at + interval '0') desc. You don't need to get your app to run this query, you can run it manually.

On second thought, seeing EXPLAIN (ANALYZE, BUFFERS) for the fast server running the slow plan might be even more useful. You can probably do that by changing the query to use ...WHERE ("big_table"."user_id" + 0 = 13) AND...

It can use one index to provide the filter, and then do the sort. Or it can use the other to provide the ORDER BY, and then stop early based on the LIMIT. It has to choose, as it can't do both. PostgreSQL has no way of knowing that everything with "big_table"."user_id" = 13 AND "big_table"."action" = 1 were also created a long time ago, so it doesn't know that the stopping early based on the LIMIT will not actually stop very early.

It is hard to figure out what your question is. You seem to know what the answer is, build the index on (user_id, action, created_at). So do that, if you want the performance problem solved.

You say you are confused, and also that you can't change the query. Being able to change the query coming from your app is irrelevant to resolving your confusion. Even if unfortunate tooling limitations prevent you from implementing a solution, that doesn't prevent you from understanding either the solution or the problem.

Are you seeking understanding, or a solution?

Both my local and the remote tables have the same number of entries (~25mio.) and about the same data distribution

There are many dimensions of data distribution. Maybe they are similar in some, but not in others. Seeing the output of EXPLAIN (ANALYZE, BUFFERS) for both servers could really help, but likely would not be enough. It would also be nice to see EXPLAIN (ANALYZE, BUFFERS) from the slow server when it is using the fast plan. You can do this by dropping the wrong index, or by changing the query so it uses ORDER BY (big_table.created_at + interval '0') desc. You don't need to get your app to run this query, you can run it manually.

On second thought, seeing EXPLAIN (ANALYZE, BUFFERS) for the fast server running the slow plan might be even more useful. You can probably do that by changing the query to use ...WHERE ("big_table"."user_id" + 0 = 13) AND...

It can use one index to provide the filter, and then do the sort. Or it can use the other to provide the ORDER BY, and then stop early based on the LIMIT. It has to choose, as it can't do both. PostgreSQL has no way of knowing that everything with "big_table"."user_id" = 13 AND "big_table"."action" = 1 were also created a long time ago, so it doesn't know that the that stopping early based on the LIMIT will not actually top very early.

It is hard to figure out what your question is. You seem to know what the answer is, build the index on (user_id, action, created_at). So do that, if you want the performance problem solved.

You say you are confused, and also that you can't change the query. Being able to change the query coming from your app is irrelevant to resolving your confusion. Even if unfortunate tooling limitations prevent you from implementing a solution, that doesn't prevent you from understanding either the solution or the problem.

Are you seeking understanding, or a solution?

Both my local and the remote tables have the same number of entries (~25mio.) and about the same data distribution

There are many dimensions of data distribution. Maybe they are similar in some, but not in others. Seeing the output of EXPLAIN (ANALYZE, BUFFERS) for both servers could really help, but likely would not be enough. It would also be nice to see EXPLAIN (ANALYZE, BUFFERS) from the slow server when it is using the fast plan. You can do this by dropping the wrong index, or by changing the query so it uses ORDER BY (big_table.created_at + interval '0') desc. You don't need to get your app to run this query, you can run it manually.

It can use one index to provide the filter, and then do the sort. Or it can use the other to provide the ORDER BY, and then stop early based on the LIMIT. It has to choose, as it can't do both. PostgreSQL has no way of knowing that everything with "big_table"."user_id" = 13 AND "big_table"."action" = 1 were also created a long time ago, so it doesn't know that the that stopping early based on the LIMIT will not actually top very early.

It is hard to figure out what your question is. You seem to know what the answer is, build the index on (user_id, action, created_at). So do that, if you want the performance problem solved.

You say you are confused, and also that you can't change the query. Being able to change the query coming from your app is irrelevant to resolving your confusion. Even if unfortunate tooling limitations prevent you from implementing a solution, that doesn't prevent you from understanding either the solution or the problem.

Are you seeking understanding, or a solution?

Both my local and the remote tables have the same number of entries (~25mio.) and about the same data distribution

There are many dimensions of data distribution. Maybe they are similar in some, but not in others. Seeing the output of EXPLAIN (ANALYZE, BUFFERS) for both servers could really help, but likely would not be enough. It would also be nice to see EXPLAIN (ANALYZE, BUFFERS) from the slow server when it is using the fast plan. You can do this by dropping the wrong index, or by changing the query so it uses ORDER BY (big_table.created_at + interval '0') desc. You don't need to get your app to run this query, you can run it manually.

On second thought, seeing EXPLAIN (ANALYZE, BUFFERS) for the fast server running the slow plan might be even more useful. You can probably do that by changing the query to use ...WHERE ("big_table"."user_id" + 0 = 13) AND...