Re: Worse performance with higher work_mem?

> On Jan 13, 2020, at 6:34 PM, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Tue, Jan 14, 2020 at 5:29 AM Israel Brewster <ijbrewster(at)alaska(dot)edu <mailto:ijbrewster(at)alaska(dot)edu>> wrote:
>>
>> I was working on diagnosing a “slow” (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='c08883c0-fbe5-11e9-bd6e-aec49259cebb'
>> AND read_time::date<='2020-01-13'::date
>> GROUP BY bucket) s1
>> ORDER BY bucket;
>>
>> In looking at the explain analyze output, I noticed that it had an “external merge Disk” sort going on, accounting for about 1 second of the runtime (explain analyze output here: 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), 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.
>
> I have noticed that after increasing the work_mem your plan has
> switched from a parallel plan to a non-parallel plan. Basically,
> earlier it was getting executed with 3 workers. And, after it becomes
> non-parallel plan execution time is 3x. For the analysis can we just
> reduce the value of parallel_tuple_cost and parallel_setup_cost and
> see how it behaves?

That was it. Setting the parallel_tuple_cost parameter to .05 and the parallel_setup_cost parameter to 500 (so, both to half their default values) caused this query to run in parallel again with the higher work_mem setting (and do the sorts in memory, as was the original goal). New explain output at https://explain.depesz.com/s/rX3m <https://explain.depesz.com/s/rX3m> Granted, doing the sorts in memory didn’t speed things up as much as I would have hoped - 5.58 seconds vs 5.9 - but at least the higher work_mem setting isn’t slowing down the query any more.

Would be nice if the query could run a little faster - even six seconds is a relatively long time to wait - but I can live with that at least. So thanks! Hmmm… now I wonder how things would change if I increased the max_parallel_workers value? Might be something to play around with. Maybe grab a few more cores for the VM.
---
Israel Brewster
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Work: 907-474-5172
cell: 907-328-9145

>
> --
> Regards,
> Dilip Kumar
> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>