Re: [HACKERS] [PATCH] Push limit to sort through a subquery

Поиск
Список
Период
Сортировка
От Douglas Doole
Тема Re: [HACKERS] [PATCH] Push limit to sort through a subquery
Дата
Msg-id CADE5jY+VaJgOxAhRXiUuj8BSO4FMj_Hrkba5dbXApvCokEi_-Q@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [HACKERS] [PATCH] Push limit to sort through a subquery  (Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>)
Ответы Re: [HACKERS] [PATCH] Push limit to sort through a subquery
Список pgsql-hackers
Дерево обсуждения
If you add this to the commitfest app, more people might look at it when the next commitfest opens.


Also, it might help if you can provide a query/ies with numbers where this optimization shows improvement.

I can't provide the real queries where we encountered the problem because they are internal. However I showed a simplified version of the queries in my first post.

On our queries, the change made quite a difference - execution time dropped from 31.4 seconds to 7.2 seconds. Explain analyze also shows that memory use dropped significantly and we didn't have to spill the sort to disk

From:

-> Sort (cost=989.95..1013.27 rows=9326 width=30) (node_startup_time/loop=31328.891, node_total_time/loop: 31329.756 rows=2001 loops=1) Buffers: temp read=772 written=11201 lsm_bufmgr hits=3392 Sort Key: *** Sort Method: external merge Sort Space Used: 89592 Sort Space Type: Disk

To:

-> Sort (cost=989.95..1013.27 rows=9326 width=30) (node_startup_time/loop=7123.275, node_total_time/loop: 7123.504 rows=2001 loops=1) Buffers: lsm_bufmgr hits=3387 Sort Key: *** Sort Method: top-N heapsort Sort Space Used: 3256 Sort Space Type: Memory

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

Предыдущее
От: Kevin Grittner
Дата:
Сообщение: Re: [HACKERS] Transition tables for triggers on foreign tables and views
Следующее
От: Alexander Korotkov
Дата:
Сообщение: Re: [HACKERS] convert EXSITS to inner join gotcha and bug