From: | Douglas Doole <dougdoole(at)gmail(dot)com> |
---|---|
To: | Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PATCH] Push limit to sort through a subquery |
Date: | 2017-04-28 21:13:23 |
Message-ID: | CADE5jY+VaJgOxAhRXiUuj8BSO4FMj_Hrkba5dbXApvCokEi_-Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
>
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Korotkov | 2017-04-28 21:37:58 | Re: convert EXSITS to inner join gotcha and bug |
Previous Message | Kevin Grittner | 2017-04-28 20:57:40 | Re: Transition tables for triggers on foreign tables and views |