[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [HACKERS] [PATCH] Push limit to sort through a subquery
From: Douglas Doole <dougdoole () gmail ! com>
Date: 2017-04-28 21:13:23
Message-ID: CADE5jY+VaJgOxAhRXiUuj8BSO4FMj_Hrkba5dbXApvCokEi_-Q () mail ! gmail ! com
[Download RAW message or body]
>
> If you add this to the commitfest app, more people might look at it when
> the next commitfest opens.
I have added it. https://commitfest.postgresql.org/14/1119/
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
[Attachment #3 (text/html)]
<div dir="ltr"><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">If you add this \
to the commitfest app, more people might look at it when the next commitfest \
opens.</blockquote><div><br></div><div>I have added it. <a \
href="https://commitfest.postgresql.org/14/1119/">https://commitfest.postgresql.org/14/1119/</a></div><div><br></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"> Also, it might help if you can provide a query/ies with \
numbers where this optimization shows \
improvement.<br></blockquote><div><br></div><div>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.</div><div><br></div><div>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</div><div><br></div><div>From:</div><div><br></div><div>-> 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<br></div><div><br></div><div>To:</div><div><br></div><div>-> 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<br></div></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic