Re: Worse performance with higher work_mem?
От | Israel Brewster |
---|---|
Тема | Re: Worse performance with higher work_mem? |
Дата | |
Msg-id | 89F686D4-DDF7-4965-8823-94823DD7B0C1@alaska.edu обсуждение исходный текст |
Ответ на | Re: Worse performance with higher work_mem? (Rob Sargent <robjsargent@gmail.com>) |
Ответы |
Re: Worse performance with higher work_mem?
Re: Worse performance with higher work_mem? |
Список | pgsql-general |
On Jan 13, 2020, at 3:46 PM, Rob Sargent <robjsargent@gmail.com> wrote:On Jan 13, 2020, at 5:41 PM, Israel Brewster <ijbrewster@alaska.edu> wrote:On Jan 13, 2020, at 3:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Israel Brewster <ijbrewster@alaska.edu> writes: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.
How can I fix this? Thanks.
Well, the brute-force way not to get that plan is "set enable_hashagg =
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.Thanks for the suggestions. Disabling hash aggregates actually made things even worse: (https://explain.depesz.com/s/cjDg), so even if that wasn’t a brute-force option, it doesn’t appear to be a good one. Creating an index on the time_bucket expression didn’t 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’s 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’t seem to make much of a difference either: https://explain.depesz.com/s/X5VTSo, to summarise:Set enable_hasagg=off: worseIndex on time_bucket expression: no change in execution time or query plan that I can seeGet 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)…so so far, it looks like the best option is to lower the work_mem, run the query, then set it back?---I don’t see that you’ve updated the statistics?
Ummmm….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’t change often. But I don’t really know.
---
Israel Brewster
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Work: 907-474-5172
cell: 907-328-9145
cell: 907-328-9145
В списке pgsql-general по дате отправления:
Предыдущее
От: Marco IppolitoДата:
Сообщение: Re: Error retrieving PostgreSQL DB information with Coturn