| From: | Mayrom Rabinovich <mayromrabinovich(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | Planing edge case for sorts with limit on non null column |
| Date: | 2026-02-05 11:23:51 |
| Message-ID: | CAH-Ro_22oaP1B8oYsaiukY3QzC3yDueB-rGGG_Rtb-QtG+hb+w@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
I am not very familiar with mailing lists so forgive me if I am committing
some sort of cardinal sin.
I found a weird edge case within this simple query:
```
-- setup table with an non null column and index on it
create table t(i serial primary key);
-- query by the reverse order of the index
explain select * from t order by i desc limit 1;
-- works as expected with the following plan:
-- "Limit (cost=0.15..0.19 rows=1 width=4)"
-- " -> Index Only Scan Backward using t_pkey on t (cost=0.15..82.41
rows=2550 width=4)"
-- same deal query by the reverse order of the index, but also specify the
wrong null order
-- from my understanding this should not matter because we don't have any
nulls on the table
-- due to the constraint.
explain select * from t order by i desc nulls last limit 1;
-- here is the issue, when I ran the following query I get this plan:
-- "Limit (cost=48.25..48.25 rows=1 width=4)"
-- " -> Sort (cost=48.25..54.63 rows=2550 width=4)"
-- " Sort Key: i DESC NULLS LAST"
-- " -> Seq Scan on t (cost=0.00..35.50 rows=2550 width=4)"
```
It seems that the planner ignores the fact that the column does not contain
nulls, and looks for a match between order of the index nulls and the order
of the nulls specified in the query, even though the nulls order is
irrelevant in this case.
I think that patching `build_index_pathkeys` would lead to the smallest
amount of changes, my concern with the patch is the fact that
`list_member_ptr` iterates over all of the pathkeys in the planner info.
I did this weird step creating an alternative pathkey and testing if its
relevant because the call sites to that function does some sort of
deduplication of useless pathkey and when I tried adding both directions of
`nulls_first` to the `retval` the last one was deduplicated (or at
least that is what I think that is happening).
I am not very familiar with the Postgres codebase but I hacked a simple
patch that from my testing, fixes the issue. But I don't know if it's the
correct place to apply that sort of logic, and I haven't written any tests
yet. The patch is very much work in progress, it's basically a toy example.
I would like to contribute if possible but I wanted to hear your opinion
before digging further into it.
Thanks,
Mayrom Rabinovich
| Attachment | Content-Type | Size |
|---|---|---|
| try_sort_with_reverse_null_order_on_non_null_columns.patch | application/octet-stream | 1.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jim Jones | 2026-02-05 11:53:22 | Re: Truncate logs by max_log_size |
| Previous Message | Rahila Syed | 2026-02-05 11:15:39 | Re: Fix pg_stat_get_backend_wait_event() for aux processes |