On Mon, Mar 6, 2017 at 6:22 AM, twoflower <standa.kurik@xxxxxxxxx> wrote:
I have the following query
select * from "JOB_MEMORY_STORAGE" st inner join "JOB_MEMORY" s on s.fk_id_storage = st.id where st.fk_id_client = 20045 order by s.id asc limit 50
The query stops as soon as it finds 50 rows which meet fk_id_client = 20045. When you order one way, it needs to cover 18883917 to find those 50. When you order the other way, it takes 6610 to find those 50. So the problem is that the tuples which satisfy st.fk_id_client = 20045 all lie towards one end of the s.id range, but PostgreSQL doesn't know that. This is a hard type of problem to solve at a fundamental level. The best you can do is work around it. Do you really need the order to be on s.id? If so, you can get PostgreSQL to stop trying to use the index for ordering purposes by writing that as "order by s.id+0 asc limit 50", or by using a CTE which does the join and have the ORDER BY and LIMIT outside the CTE.
Do you have an index on fk_id_client? Or perhaps better, (fk_id_client, id)? How many rows satisfy fk_id_client = 20045?
How can I help Postgres execute the query with asc ordering as fast as the one with desc?
You probably can't. Your data us well suited to one, and ill suited for the other. You can probably make it faster than it currently is, but not as fast as the DESC version.
Cheers,
Jeff
