Re: Worse performance with higher work_mem?
Israel Brewster <[email protected]> Tue, 14 Jan 2020 08:08:29 -0900
| Newsgroups | gmane.comp.db.postgresql.general |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail=_9FC09936-D8AA-4786-ACBF-74FCFB6FF969 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 >=20 > On Jan 13, 2020, at 3:46 PM, Rob Sargent <[email protected]> = wrote: >=20 >=20 >=20 >> On Jan 13, 2020, at 5:41 PM, Israel Brewster <[email protected] = <mailto:[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? >> --- >=20 > I don=E2=80=99t see that you=E2=80=99ve updated the statistics? Ummmm=E2=80=A6.no. I know nothing about that :-) Some research tells me that a) it should happen as part of the = autovacuum process, and that b) I may not be running autovacuum enough, = since it is a large table and doesn=E2=80=99t change often. But I = don=E2=80=99t really know. --- 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=_9FC09936-D8AA-4786-ACBF-74FCFB6FF969 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""><br = class=3D"">On Jan 13, 2020, at 3:46 PM, Rob Sargent <<a = href=3D"mailto:[email protected]" = class=3D"">[email protected]</a>> wrote:</div><br = class=3D"Apple-interchange-newline"><div class=3D""><br = class=3D"Apple-interchange-newline"><br class=3D"" style=3D"caret-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;"><blockquote type=3D"cite" class=3D"" = style=3D"font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = orphans: auto; text-align: start; text-indent: 0px; text-transform: = none; white-space: normal; widows: auto; word-spacing: 0px; = -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; = text-decoration: none;"><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 class=3D"" = 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;"><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 = style=3D"caret-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;" class=3D""><br = class=3D""></div><span style=3D"caret-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; float: none; display: inline !important;" class=3D"">I don=E2=80=99t= see that you=E2=80=99ve updated the = statistics?</span></div></blockquote><br = class=3D""></div><div>Ummmm=E2=80=A6.no. I know nothing about that = :-)</div><div><br class=3D""></div><div>Some research tells me that a) = it should happen as part of the autovacuum process, and that b) I may = not be running autovacuum enough, since it is a large table and = doesn=E2=80=99t change often. But I don=E2=80=99t really = know.</div><div><br class=3D""></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""></body></html>= --Apple-Mail=_9FC09936-D8AA-4786-ACBF-74FCFB6FF969--