Re: Worse performance with higher work_mem?
Rob Sargent <[email protected]> Mon, 13 Jan 2020 17:46:26 -0700
| Newsgroups | gmane.comp.db.postgresql.general |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail=_B910EFE0-445B-49B6-ABB4-4D75FB605484 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jan 13, 2020, at 5:41 PM, Israel Brewster <[email protected]> = wrote: >=20 >> On Jan 13, 2020, at 3:19 PM, Tom Lane <[email protected] = <mailto:[email protected]>> wrote: >>=20 >> Israel Brewster <[email protected] = <mailto:[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> = <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> = <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 >=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> >=20 > So, to summarise: >=20 > 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) >=20 > =E2=80=A6so so far, it looks like the best option is to lower the = work_mem, run the query, then set it back? > --- I don=E2=80=99t see that you=E2=80=99ve updated the statistics? --Apple-Mail=_B910EFE0-445B-49B6-ABB4-4D75FB605484 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""><br = class=3D""><div><br class=3D""><blockquote type=3D"cite" class=3D""><div = class=3D"">On Jan 13, 2020, at 5:41 PM, Israel Brewster <<a = href=3D"mailto:[email protected]" = class=3D"">[email protected]</a>> wrote:</div><br = class=3D"Apple-interchange-newline"><div class=3D""><div = style=3D"caret-color: rgb(0, 0, 0); font-family: CourierNewPSMT; = font-size: 14px; 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;" class=3D""><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:<span class=3D"Apple-converted-space"> </span><a = href=3D"https://explain.depesz.com/s/jx0q" = class=3D"">https://explain.depesz.com/s/jx0q</a><span = class=3D"Apple-converted-space"> </span><<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><span = class=3D"Apple-converted-space"> </span><<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 class=3D""><br = class=3D""></div><div class=3D"">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 class=3D""><br = class=3D""></div><div class=3D"">So, to summarise:</div><div = class=3D""><br class=3D""></div><div class=3D"">Set enable_hasagg=3Doff: = worse</div><div class=3D"">Index on time_bucket expression: no change in = execution time or query plan that I can see</div><div class=3D"">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 class=3D"">Lower work_mem, forcing a disk sort and = completely different query plan: Way, way better (around 6 = seconds)</div><div class=3D""><br class=3D""></div><div class=3D"">=E2=80=A6= so so far, it looks like the best option is to lower the work_mem, run = the query, then set it back?</div><div class=3D""><div = class=3D"">---</div></div></div></div></blockquote><div><br = class=3D""></div>I don=E2=80=99t see that you=E2=80=99ve updated the = statistics?</div><div><br class=3D""></div><div><br = class=3D""></div></body></html>= --Apple-Mail=_B910EFE0-445B-49B6-ABB4-4D75FB605484--