Re: Cost Model

2017-12-20 17:34 GMT-02:00 Andres Freund <andres(at)anarazel(dot)de>:

> On 2017-12-20 17:13:31 -0200, neto brpr wrote:
> > Just to explain it better. The idea of ​​differentiating read and write
> > parameters (sequential and random) is exactly so that the access plans
> can
> > be better chosen by the optimizer. But for this, the Hash join, merge
> join,
> > sorting and other algorithms should also be changed to consider these new
> > parameters.
>
> I'm doubtful that there's that much benefit. Mergejoin doesn't write,
> hashjoins commonly don't write , and usually if so there's not that many
> alternatives to batched hashjoins. Similar-ish with sorts, although
> sometimes that can instead be done using ordered index scans.
>

Dear Andres
By reading some cientific paper, it has been said that the hash join and
sort merge join algorithms perform better than nested loop, considering
that it runs on an HDD, since the costs of read and write are practically
the same (symmetrical). However, in an SSD, where the cost of write is
double the cost of reads, this is not true, since both algorithms (sort
merge and hash join) require some writing operations on disk, when the data
does not fit in memory RAM. If we consider that the nested loop that works
only with read, and in case there is an index for the internal table, the
nested loop this would be a good alternative, since the readings on SSDs
are many times faster than in HDDs. This is an example of a situation in
which the difference between reading and writing could make the Optimizer
choose the Nested Loop rather than the Hash Join.

Regards

> What are the cases you forsee where costing reads/writes differently
> will lead to better plans?
>
> Greetings,
>
> Andres Freund
>

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Livre
de vírus. www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>.
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>