Re: [PERFORM] Huge difference between ASC and DESC ordering

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: [PERFORM] Huge difference between ASC and DESC ordering
Дата
Msg-id CAMkU=1xyzqr=LqeGRpk+ao2w0_aFyFF+wrqjhiX9Ti_nppDwBg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [PERFORM] Huge difference between ASC and DESC ordering  (twoflower <standa.kurik@gmail.com>)
Ответы Re: [PERFORM] Huge difference between ASC and DESC ordering
Список pgsql-performance
Дерево обсуждения
On Mon, Mar 6, 2017 at 8:46 AM, twoflower <standa.kurik@gmail.com> wrote:
Thank you Jeff.

There are 7 million rows satisfying fk_id_client = 20045. There is an index on fk_id_client, now I added a composite (fk_id_client, id) index but that did not help.

With 7 million rows, you shouldn't expect any magic here.  But still 7 million is less than 18 million, and you may be able to get that 7 million with more sequential-like IO.

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.



I see the point of what you are saying, but still don't understand how these two situations (asc vs. desc) are not symmetrical.

They return different data.  How could they be symmetrical?  You are getting a different 50 rows depending on which way you order the data in the query.  You are **not** getting the same 50 rows, just in a different order from among the 50.

 
I mean, there is an ascending index on JOB_MEMORY.id, so why does it matter which end I am picking the data from?


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.
 

The thing is, even when I force Postgres to use the ascending index on id, it's still orders of magnitude slower than the desc version (even when that one goes through the index backwards).

Right.  PostgreSQL has to return the rows commanded by your query.  It can't just decide to return a different set of rows because doing so would be faster.  If that is what you want, wrap the whole query into a subselect and move the ORDER BY into the outer query, like "select * from (SELECT ... LIMIT 50) foo order by foo.id"

Changing the ordering direction of the index doesn't change which rows get returned, while changing the ordering direction of the query does.

Cheers,

Jeff

В списке pgsql-performance по дате отправления:

Предыдущее
От: twoflower
Дата:
Сообщение: Re: [PERFORM] Huge difference between ASC and DESC ordering
Следующее
От: "Sven R. Kunze"
Дата:
Сообщение: Re: [PERFORM] Speeding up JSON + TSQUERY + GIN