CLUSTER using sequential scan followed by sorting

Question

I'm tring to understand the meaning of the following clause from PostgreSQL 12 docs

CLUSTER can re-sort the table using either an index scan on the specified index, or (if the index is a b-tree) a sequential scan followed by sorting.

When a sequential scan and sort is used, a temporary sort file is also created, so that the peak temporary space requirement is as much as double the table size, plus the index sizes. ... you can disable this choice by temporarily setting enable_sort to off.

Clustering is meant to physically reoder the table based on the index information. So what is going to happen if I set enable_sort=off? Will it still manage to reoder the data properly? If yes, then what is the benifit of having the enable_sort option in the play here at all?

Answer 1

So what is going to happen if I set enable_sort=off?

It will possibly hop back and forth between the index and random spots in the table, grinding our hard disk into dust while slowing reordering the table (by creating a new copy which is in index order).

Will it still manage to reoder the data properly? If yes, then what is the benifit of having the enable_sort option in the play here at all?

Yes. performance. If the table is huge and not already well-clustered, bulk sort is much faster than following an index.

Answer 2

TFM says:

It is impossible to suppress explicit sorts entirely, but turning this variable off discourages the planner from using one if there are other methods available.

So yes, it will reorder the table rows properly, but will try to chose an alternative, potentially slower, access method, if at all possible.