Re: Huge difference between ASC and DESC ordering

Thank you Jeff.

Jeff Janes wrote
> 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.

Yes, this

finishes in 20 seconds, which is two times faster than *order by id asc*.
Query plan:

Jeff Janes wrote
> 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.

I think I finally get it. I investigated the query result set more closely
and realized that indeed the relevant rows start only after > 18 million
rows in the asc *id* order and that's the problem. On the other hand, with
*desc* Postgres very quickly finds 50 rows matching *fk_id_client = 20045*.
So it is just the process of scanning the index and checking the condition
which takes all of the time.

Understanding the problem more, it brought me to a solution I might end up
going with (and which you also suggested by asking whether I really need
ordering the data by *id*), a different order clause which still makes sense
in my scenario:

Finishes in 7 seconds.

Best regards,
Stanislav

--
View this message in context: http://www.postgresql-archive.org/Huge-difference-between-ASC-and-DESC-ordering-tp5947712p5947887.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.