Worse performance with higher work_mem?

From: Israel Brewster <ijbrewster(at)alaska(dot)edu>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Worse performance with higher work_mem?
Date: 2020-01-13 23:58:58
Message-ID: 17AAA60F-6CC8-4D5A-9944-9C144A81E609@alaska.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 <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.

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.
---
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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2020-01-14 00:19:37 Re: Worse performance with higher work_mem?
Previous Message Andreas Kretschmer 2020-01-13 20:28:45 Re: pg_stat_statements extension