| From: | Raja Sai pranav <rajasaipranav0(at)gmail(dot)com> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Cc: | imchifan8(at)gmail(dot)com |
| Subject: | Re: BUG #19881: CLUSTER uses a disabled sort path when enable_sort is off |
| Date: | 2026-11-20 17:12:34 |
| Message-ID: | nh-a8ecf828-5d53-4cf4-b046-4f25bb4207b7@google.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
Hi,
I reproduced this on master (4a26e36f87) and attached a patch.
plan_cluster_use_sort() compares only total_cost of the seqscan + sort path
against the index scan path. enable_sort = off no longer adds a big cost to
the sort; it adds a disabled node, which this comparison ignores. The patch
uses compare_path_costs(), which looks at disabled nodes first, like the
rest of the planner.
With your settings (random_page_cost = 100000, seq_page_cost = 0.01) I
tried all four combinations of enable_sort and enable_indexscan. Only
enable_sort = off with enable_indexscan = on changes, and it now uses the
index scan. With both off, it falls back to comparing cost, as before.
The patch adds a test to cluster.sql that checks the CLUSTER (VERBOSE)
message ("using index scan" vs "using sequential scan and sort"). It uses
an empty table and VERBOSITY terse, so the output has no page counts or
timings. The test fails without the fix, and the regression tests pass with
it. REL_18_STABLE has the same code.
Regards,
Sai Pranav
On Fri, Nov 20, 2026 10:41 PM, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 19881
> Logged by: Noah
> Email address: imchifan8(at)gmail(dot)com
> PostgreSQL version: 18.6
> Operating system: Linux/amd64
> Description:
>
> CLUSTER can choose a sequential scan plus explicit sort even when
> enable_sort is off and an enabled index-scan alternative exists. In the
> case
> below, high random_page_cost makes the sort route numerically cheaper, but
> its additional disabled-node count should take precedence over total cost
> and cause the index route to be selected.
>
> With enable_sort on, trace_sort reports "begin tuple sort", as expected.
> With enable_sort off, the same message still appears, showing that CLUSTER
> selected the disabled explicit-sort route.
>
> How to repeat:
>
> Save the following as setup.sql:
>
> ```
> DROP TABLE IF EXISTS cluster_control;
> DROP TABLE IF EXISTS cluster_test;
> CREATE TABLE cluster_control (a integer, filler text);
> CREATE TABLE cluster_test (a integer, filler text);
> INSERT INTO cluster_control
> SELECT g, repeat('x', 80) FROM generate_series(1, 50000) AS g;
> INSERT INTO cluster_test
> SELECT * FROM cluster_control;
> CREATE INDEX cluster_control_idx ON cluster_control (a);
> CREATE INDEX cluster_test_idx ON cluster_test (a);
> ANALYZE cluster_control;
> ANALYZE cluster_test;
> ```
>
> Save the control as control.sql:
>
> ```
> SET client_min_messages = log;
> SET trace_sort = on;
> SET random_page_cost = 100000;
> SET seq_page_cost = 0.01;
> SET enable_sort = on;
> CLUSTER cluster_control USING cluster_control_idx;
> ```
>
> Save the test as test.sql:
>
> ```
> SET client_min_messages = log;
> SET trace_sort = on;
> SET random_page_cost = 100000;
> SET seq_page_cost = 0.01;
> SET enable_sort = off;
> CLUSTER cluster_test USING cluster_test_idx;
> ```
>
> Run the files in order, with the control and test in separate sessions:
>
> ```
> psql -X -d postgres -f setup.sql
> psql -X -d postgres -f control.sql
> psql -X -d postgres -f test.sql
> ```
>
> Both CLUSTER commands complete. The decisive recorded output for the
> control
> includes:
>
> ```
> SET
> SET
> SET
> SET
> SET
> CLUSTER
> LOG: begin tuple sort: nkeys = 1, workMem = 65536, randomAccess = f
> ```
>
> The test produces the same strategy discriminator despite enable_sort being
> off:
>
> ```
> SET
> SET
> SET
> SET
> SET
> CLUSTER
> LOG: begin tuple sort: nkeys = 1, workMem = 65536, randomAccess = f
> ```
>
> Other trace_sort messages report index sorts used while rebuilding indexes;
> the "begin tuple sort" message identifies the explicit sort used to
> implement CLUSTER itself.
>
> This was reproduced on PostgreSQL 18.6 on Linux/amd64. The behavior
> suggests
> that CLUSTER's manual comparison of its alternatives uses numeric cost
> without first honoring the disabled-node count. This occurs when CLUSTER
> chooses between an index scan and a numerically cheaper sequential scan
> plus
> sort while explicit sorts are disabled.
>
>
>
>
>
>
>
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Honor-disabled-nodes-when-CLUSTER-chooses-between.patch | text/x-patch | 3.6 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Sami Imseih | 2026-11-20 17:19:47 | Re: Anti-wraparound autovacuum stops for good after "no slot available for new autovacuum worker process" |
| Previous Message | Raja Sai pranav | 2026-11-20 17:07:11 | Re: BUG #19879: Blocked COPY FROM reports NULL command and type in pg_stat_progress_copy |