From: twoflower <standa.kurik@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Huge difference between ASC and DESC ordering
Date: Mon, 6 Mar 2017 07:22:26 -0700 (MST)
Message-ID: <1488810146219-5947712.post@n3.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
I have the following query
which takes 90 seconds to finish. *JOB_MEMORY* has 45 million rows,
*JOB_MEMORY_STORAGE* has 50 000 rows.
Query plan:
As you can see, it is indeed using an index *JOB_MEMORY_id_desc* in a
backward direction, but it is very slow.
When I change ordering to *desc* in the query, the query finishes
immediately and the query plan is
There is also an index on *JOB_MEMORY.id*. I also tried a composite index on
*(fk_id_storage, id)*, but it did not help (and was not actually used).
I ran *ANALYZE* on both tables.
Postgres 9.6.2, Ubuntu 14.04, 192 GB RAM, SSD, shared_buffers = 8196 MB.
How can I help Postgres execute the query with *asc* ordering as fast as the
one with *desc*?
Thank you.
--
View this message in context: http://www.postgresql-archive.org/Huge-difference-between-ASC-and-DESC-ordering-tp5947712.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
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: <1488810146219-5947712.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