Re: [PERFORM] 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.