Re: Disk-based hash aggregate's cost model
От | Tomas Vondra |
---|---|
Тема | Re: Disk-based hash aggregate's cost model |
Дата | |
Msg-id | 20200830002620.pabcrarjbv3j3dlj@development обсуждение исходный текст |
Ответ на | Re: Disk-based hash aggregate's cost model (Jeff Davis <pgsql@j-davis.com>) |
Ответы |
Re: Disk-based hash aggregate's cost model
|
Список | pgsql-hackers |
On Fri, Aug 28, 2020 at 06:32:38PM -0700, Jeff Davis wrote: >On Thu, 2020-08-27 at 17:28 -0700, Peter Geoghegan wrote: >> We have a Postgres 13 open item for Disk-based hash aggregate, which >> is the only non-trivial open item. There is a general concern about >> how often we get disk-based hash aggregation when work_mem is >> particularly low, and recursion seems unavoidable. This is generally >> thought to be a problem in the costing. > >We discussed two approaches to tweaking the cost model: > >1. Penalize HashAgg disk costs by a constant amount. It seems to be >chosen a little too often, and we can reduce the number of plan >changes. > >2. Treat recursive HashAgg spilling skeptically, and heavily penalize >recursive spilling. > >The problem with approach #2 is that we have a default hash mem of 4MB, >and real systems have a lot more than that. In this scenario, recursive >spilling can beat Sort by a lot. > I think the main issue is that we're mostly speculating what's wrong. I've shared some measurements and symptoms, and we've discussed what might be causing it, but I'm not really sure we know for sure. I really dislike (1) because it seems more like "We don't know what's wrong so we'll penalize hashagg," kind of solution. A much more principled solution would be to tweak the costing accordingly, not just by adding some constant. For (2) it really depends if recursive spilling is really the problem here. In the examples I shared, the number of partitions/batches was very different, but the query duration was mostly independent (almost constant). FWIW I still haven't seen any explanation why the current code spills more data than the CP_SMALL_TLIST patch (which was reverted). regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
В списке pgsql-hackers по дате отправления: