From: twoflower <standa.kurik@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Huge difference between ASC and DESC ordering
Date: Tue, 7 Mar 2017 01:40:18 -0700 (MST)
Message-ID: <1488876018251-5947887.post@n3.nabble.com> (raw)
In-Reply-To: <CAMkU=1xyzqr=LqeGRpk+ao2w0_aFyFF+wrqjhiX9Ti_nppDwBg@mail.gmail.com>
References: <1488810146219-5947712.post@n3.nabble.com>
<CAMkU=1yThfvCjHCtvFxGzf3GUeqqTQA+ftzshZ_NwcnkFjjN7w@mail.gmail.com>
<1488818792254-5947737.post@n3.nabble.com>
<CAMkU=1xyzqr=LqeGRpk+ao2w0_aFyFF+wrqjhiX9Ti_nppDwBg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.ht...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: standa.kurik@gmail.com
Subject: Re: Huge difference between ASC and DESC ordering
In-Reply-To: <1488876018251-5947887.post@n3.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox