Re: Huge difference between ASC and DESC ordering

On Mon, Mar 6, 2017 at 6:22 AM, twoflower <standa(dot)kurik(at)gmail(dot)com> 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