Re: Worse performance with higher work_mem?

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 seco=
nd 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. Wher=
eupon the query plan got much simpler, and performance of said query compl=
etely tanked, increasing to about 15.5 seconds runtime (https://explain.de=
pesz.com/s/Kl0S <https://explain.depesz.com/s/Kl0S>), most of which was in=
 a HashAggregate.
> How can I fix this? Thanks.

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.

			regards, tom lane