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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
