Re: Trouble with hashagg spill I/O pattern and costing

On Mon, 2020-05-25 at 14:17 +0200, Tomas Vondra wrote:
> It's still ~2x slower than the sort, so presumably we'll need to
> tweak
> the costing somehow.

One thing to think about is that the default random_page_cost is only
4X seq_page_cost. We know that's complete fiction, but it's meant to
paper over the OS caching effects. It seems like that shortcut may be
what's hurting us now.

HashAgg counts 1/2 of the page accesses as random, whereas Sort only
counts 1/4 as random. If the random_page_cost were closer to reality,
HashAgg would already be penalized substantially. It might be
interesting to test with higher values of random_page_cost and see what
the planner does.

If we want to be a bit more conservative, I'm fine with adding a
general penalty against a HashAgg that we expect to spill (multiply the
disk costs by some factor). We can consider removing the penalty in
v14.

> I do belive this is still due to differences in I/O
> patterns, with parallel hashagg probably being a bit more random (I'm
> deducing that from SSD not being affected by this).

Do you think the difference in IO patterns is due to a difference in
handling reads vs. writes in the kernel? Or do you think that 128
blocks is not enough to amortize the cost of a seek for that device?

Regards,
Jeff Davis