Re: Cost Model - Mailing list pgsql-hackers
2017-12-20 17:34 GMT-02:00 Andres Freund <andres@anarazel.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
pgsql-hackers by date: