Re: Huge difference between ASC and DESC ordering

On Mon, Mar 6, 2017 at 8:46 AM, twoflower <standa(dot)kurik(at)gmail(dot)com> wrote:

> Thank you Jeff.
>
> There are 7 million rows satisfying fk_id_client = 20045. There is an
> index on fk_id_client, now I added a composite (fk_id_client, id) index but
> that did not help.
>

With 7 million rows, you shouldn't expect any magic here. But still 7
million is less than 18 million, and you may be able to get that 7 million
with more sequential-like IO.

Did you force PostgreSQL to stop using the index on s.id? If not, do
that. If so, please post the EXPLAIN (analyze) of the plan it does switch
to.

> I see the point of what you are saying, but still don't understand how
> these two situations (*asc* vs. *desc*) are not symmetrical.

They return different data. How could they be symmetrical? You are
getting a different 50 rows depending on which way you order the data in
the query. You are **not** getting the same 50 rows, just in a different
order from among the 50.

> I mean, there *is* an ascending index on *JOB_MEMORY.id*, so why does it
> matter which end I am picking the data from?
>

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. This
fact does not depend on whether the index is ASC or DESC. If you traverse
a DESC index backwards, it has exactly the same issue as if you traverse a
ASC index forward. Either way, once it decides to use that index to obtain
the ordering of the query, it has to inspect 18883917 tuples before it
satisfies the LIMIT.

>
> The thing is, even when I force Postgres to use the ascending index on
> *id*, it's still orders of magnitude slower than the *desc* version (even
> when that one goes through the index backwards).

Right. PostgreSQL has to return the rows commanded by your query. It
can't just decide to return a different set of rows because doing so would
be faster. If that is what you want, wrap the whole query into a subselect
and move the ORDER BY into the outer query, like "select * from (SELECT ...
LIMIT 50) foo order by foo.id"

Changing the ordering direction of the index doesn't change which rows get
returned, while changing the ordering direction of the query does.

Cheers,

Jeff