Worse performance with higher work_mem?
Israel Brewster <[email protected]> Mon, 13 Jan 2020 14:58:58 -0900
| Newsgroups | gmane.comp.db.postgresql.general |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail=_0FD5E72A-E1F0-4266-838D-7071B511C170
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=utf-8
I was working on diagnosing a =E2=80=9Cslow=E2=80=9D (about 6 second run =
time) query:
SELECT
to_char(bucket,'YYYY-MM-DD"T"HH24:MI:SS') as dates,
x_tilt,
y_tilt,
rot_x,
rot_y,
date_part('epoch', bucket) as timestamps,
temp
FROM
(SELECT
time_bucket('1 week', read_time) as bucket,
avg(tilt_x::float) as x_tilt,
avg(tilt_y::float) as y_tilt,
avg(rot_x::float) as rot_x,
avg(rot_y::float) as rot_y,
avg(temperature::float) as temp
FROM tilt_data
WHERE station=3D'c08883c0-fbe5-11e9-bd6e-aec49259cebb'
AND read_time::date<=3D'2020-01-13'::date =20
GROUP BY bucket) s1
ORDER BY bucket;
In looking at the explain analyze output, I noticed that it had an =
=E2=80=9Cexternal merge Disk=E2=80=9D sort going on, accounting for =
about 1 second of the runtime (explain analyze output here: =
https://explain.depesz.com/s/jx0q <https://explain.depesz.com/s/jx0q>). =
Since the machine has plenty of RAM available, I went ahead and =
increased the work_mem parameter. Whereupon the query plan got much =
simpler, and performance of said query completely tanked, increasing to =
about 15.5 seconds runtime (https://explain.depesz.com/s/Kl0S =
<https://explain.depesz.com/s/Kl0S>), most of which was in a =
HashAggregate.
I am running PostgreSQL 11.6 on a machine with 128GB of ram (so, like I =
said, plenty of RAM)
How can I fix this? Thanks.
---
Israel Brewster
Software Engineer
Alaska Volcano Observatory=20
Geophysical Institute - UAF=20
2156 Koyukuk Drive=20
Fairbanks AK 99775-7320
Work: 907-474-5172
cell: 907-328-9145
--Apple-Mail=_0FD5E72A-E1F0-4266-838D-7071B511C170
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
charset=utf-8
<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;" class=3D"">I =
was working on diagnosing a =E2=80=9Cslow=E2=80=9D (about 6 second run =
time) query:<div class=3D""><br class=3D""></div><div class=3D"">SELECT<br=
class=3D""> =
to_char(bucket,'YYYY-MM-DD"T"HH24:MI:SS') as dates,<br =
class=3D""> x_tilt,<br =
class=3D""> y_tilt,<br =
class=3D""> rot_x,<br =
class=3D""> rot_y,<br =
class=3D""> =
date_part('epoch', bucket) as timestamps,<br class=3D""> =
temp<br class=3D""> =
FROM<br class=3D""> =
(SELECT<br class=3D""> =
time_bucket('1 week', read_time) as bucket,<br =
class=3D""> =
avg(tilt_x::float) as x_tilt,<br class=3D""> =
avg(tilt_y::float) as y_tilt,<br =
class=3D""> =
avg(rot_x::float) as rot_x,<br class=3D""> =
avg(rot_y::float) as rot_y,<br =
class=3D""> =
avg(temperature::float) as temp<br class=3D""> =
FROM tilt_data<br class=3D""> =
WHERE =
station=3D'c08883c0-fbe5-11e9-bd6e-aec49259cebb'<br class=3D""> =
AND =
read_time::date<=3D'2020-01-13'::date <br =
class=3D""> GROUP BY =
bucket) s1<br class=3D""> ORDER BY =
bucket;</div><div class=3D""><br class=3D""></div><div class=3D"">In =
looking at the explain analyze output, I noticed that it had an =
=E2=80=9Cexternal merge Disk=E2=80=9D sort going on, accounting for =
about 1 second of the runtime (explain analyze output here: <a =
href=3D"https://explain.depesz.com/s/jx0q" =
class=3D"">https://explain.depesz.com/s/jx0q</a>). Since the machine has =
plenty of RAM available, I went ahead and increased the work_mem =
parameter. Whereupon the query plan got much simpler, and performance of =
said query completely tanked, increasing to about 15.5 seconds runtime =
(<a href=3D"https://explain.depesz.com/s/Kl0S" =
class=3D"">https://explain.depesz.com/s/Kl0S</a>), most of which was in =
a HashAggregate.</div><div class=3D""><br class=3D""></div><div =
class=3D"">I am running PostgreSQL 11.6 on a machine with 128GB of ram =
(so, like I said, plenty of RAM)</div><div class=3D""><br =
class=3D""></div><div class=3D"">How can I fix this? Thanks.<br =
class=3D""><div class=3D"">
<div dir=3D"auto" style=3D"word-wrap: break-word; -webkit-nbsp-mode: =
space; line-break: after-white-space;" class=3D""><div =
style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: =
Helvetica; font-size: 12px; font-style: normal; font-variant-caps: =
normal; font-weight: normal; letter-spacing: normal; text-align: start; =
text-indent: 0px; text-transform: none; white-space: normal; =
word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: =
none;">---</div><div style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, =
0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; =
font-variant-caps: normal; font-weight: normal; letter-spacing: normal; =
text-align: start; text-indent: 0px; text-transform: none; white-space: =
normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; =
text-decoration: none;">Israel Brewster<br class=3D"">Software =
Engineer<br class=3D"">Alaska Volcano Observatory <br =
class=3D"">Geophysical Institute - UAF <br class=3D"">2156 Koyukuk =
Drive <br class=3D"">Fairbanks AK 99775-7320</div><div =
style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: =
Helvetica; font-size: 12px; font-style: normal; font-variant-caps: =
normal; font-weight: normal; letter-spacing: normal; text-align: start; =
text-indent: 0px; text-transform: none; white-space: normal; =
word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: =
none;">Work: 907-474-5172<br class=3D"">cell: =
907-328-9145</div></div>
</div>
<br class=3D""></div></body></html>=
--Apple-Mail=_0FD5E72A-E1F0-4266-838D-7071B511C170--