Re: Worse performance with higher work_mem?
Israel Brewster <[email protected]> Mon, 13 Jan 2020 15:41:41 -0900
| Newsgroups | gmane.comp.db.postgresql.general |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail=_A6E2A5C3-BFAF-4401-8A03-725597A3C805 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jan 13, 2020, at 3:19 PM, Tom Lane <[email protected]> wrote: >=20 > Israel Brewster <[email protected]> writes: >> 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. >> How can I fix this? Thanks. >=20 > Well, the brute-force way not to get that plan is "set enable_hashagg = =3D > false". But it'd likely be a better idea to try to improve the = planner's > rowcount estimates. The problem here seems to be lack of stats for > either "time_bucket('1 week', read_time)" or "read_time::date". > In the case of the latter, do you really need a coercion to date? > If it's a timestamp column, I'd think not. As for the former, > if the table doesn't get a lot of updates then creating an expression > index on that expression might be useful. >=20 Thanks for the suggestions. Disabling hash aggregates actually made = things even worse: (https://explain.depesz.com/s/cjDg = <https://explain.depesz.com/s/cjDg>), so even if that wasn=E2=80=99t a = brute-force option, it doesn=E2=80=99t appear to be a good one. Creating = an index on the time_bucket expression didn=E2=80=99t seem to make any = difference, and my data does get a lot of additions (though virtually no = changes) anyway (about 1 additional record per second). As far as = coercion to date, that=E2=80=99s so I can do queries bounded by date, = and actually have all results from said date included. That said, I = could of course simply make sure that when I get a query parameter of, = say, 2020-1-13, I expand that into a full date-time for the end of the = day. However, doing so for a test query didn=E2=80=99t seem to make much = of a difference either: https://explain.depesz.com/s/X5VT = <https://explain.depesz.com/s/X5VT> So, to summarise: Set enable_hasagg=3Doff: worse Index on time_bucket expression: no change in execution time or query = plan that I can see Get rid of coercion to date: *slight* improvement. 14.692 seconds = instead of 15.5 seconds. And it looks like the row count estimates were = actually worse. Lower work_mem, forcing a disk sort and completely different query plan: = Way, way better (around 6 seconds) =E2=80=A6so so far, it looks like the best option is to lower the = work_mem, run the query, then set it back? --- 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 > regards, tom lane --Apple-Mail=_A6E2A5C3-BFAF-4401-8A03-725597A3C805 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""><div><blockquote type=3D"cite" class=3D""><div class=3D"">On = Jan 13, 2020, at 3:19 PM, Tom Lane <<a = href=3D"mailto:[email protected]" class=3D"">[email protected]</a>> = wrote:</div><br class=3D"Apple-interchange-newline"><div class=3D""><div = class=3D"">Israel Brewster <<a href=3D"mailto:[email protected]" = class=3D"">[email protected]</a>> writes:<br class=3D""><blockquote= type=3D"cite" 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> <<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> <<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.<br class=3D"">How can I fix this? Thanks.<br = class=3D""></blockquote><br class=3D"">Well, the brute-force way not to = get that plan is "set enable_hashagg =3D<br class=3D"">false". But = it'd likely be a better idea to try to improve the planner's<br = class=3D"">rowcount estimates. The problem here seems to be lack = of stats for<br class=3D"">either "time_bucket('1 week', read_time)" or = "read_time::date".<br class=3D"">In the case of the latter, do you = really need a coercion to date?<br class=3D"">If it's a timestamp = column, I'd think not. As for the former,<br class=3D"">if the = table doesn't get a lot of updates then creating an expression<br = class=3D"">index on that expression might be useful.<br class=3D""><br = class=3D""></div></div></blockquote><div><br class=3D""></div><div>Thanks = for the suggestions. Disabling hash aggregates actually made things even = worse: (<a href=3D"https://explain.depesz.com/s/cjDg" = class=3D"">https://explain.depesz.com/s/cjDg</a>), so even if that = wasn=E2=80=99t a brute-force option, it doesn=E2=80=99t appear to be a = good one. Creating an index on the time_bucket expression didn=E2=80=99t = seem to make any difference, and my data does get a lot of additions = (though virtually no changes) anyway (about 1 additional record per = second). As far as coercion to date, that=E2=80=99s so I can do queries = bounded by date, and actually have all results from said date included. = That said, I could of course simply make sure that when I get a query = parameter of, say, 2020-1-13, I expand that into a full date-time for = the end of the day. However, doing so for a test query didn=E2=80=99t = seem to make much of a difference either: <a = href=3D"https://explain.depesz.com/s/X5VT" = class=3D"">https://explain.depesz.com/s/X5VT</a></div><div><br = class=3D""></div><div>So, to summarise:</div><div><br = class=3D""></div><div>Set enable_hasagg=3Doff: worse</div><div>Index on = time_bucket expression: no change in execution time or query plan that I = can see</div><div>Get rid of coercion to date: *slight* improvement. = 14.692 seconds instead of 15.5 seconds. And it looks like the row count = estimates were actually worse.</div><div>Lower work_mem, forcing a disk = sort and completely different query plan: Way, way better (around 6 = seconds)</div><div><br class=3D""></div><div>=E2=80=A6so so far, it = looks like the best option is to lower the work_mem, run the query, then = set it back?</div><div><div>---</div><div>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>Work: 907-474-5172<br class=3D"">cell: = 907-328-9145</div></div><br class=3D""><blockquote type=3D"cite" = class=3D""><div class=3D""><div class=3D""><span class=3D"Apple-tab-span" = style=3D"white-space:pre"> </span><span class=3D"Apple-tab-span" = style=3D"white-space:pre"> </span><span class=3D"Apple-tab-span" = style=3D"white-space:pre"> </span>regards, tom lane<br = class=3D""></div></div></blockquote></div><br class=3D""></body></html>= --Apple-Mail=_A6E2A5C3-BFAF-4401-8A03-725597A3C805--