Why does ORDER BY NULLS LAST affect the query plan on a primary key?

Using PostgreSQL 11, I have the following table with around 450 million rows:

```
postgres=> \d+ sales
Table "public.sales"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------------------------+-----------------------------+------------------------------------+----------+--------------+-------------
created_terminal_id | integer | not null | plain | |
company_id | integer | not null | plain | |
customer_id | integer | | plain | |
sale_no | character varying(20) | not null | extended | |
sale_type | smallint | not null | plain | |
source_type | smallint | not null | plain | |
sale_date | timestamp without time zone | not null | plain | |
paid_amount | numeric(18,4) | not null default 0.0000 | main | |
change_amount | numeric(18,4) | not null default 0.0000 | main | |
cashup_id | integer | | plain | |
staff_id | integer | | plain | |
payment_terminal_id | integer | not null

An index can only be used to process an ORDER BY clause without sorting if the index is in the same order as specified by ORDER BY.

Now your index is (by default) sorted ASC NULLS LAST, and since an index can be scanned in both directions, it can support both ORDER BY sale_id ASC NULLS LAST and ORDER BY sale_id DESC NULLS FIRST. But since the ordering is different, it cannot support ORDER BY sale_id DESC NULLS LAST.

The planner does not take the NOT NULL of the column definition into account. This is determined in build_index_pathkeys in src/backend/optimizer/path/pathkeys.c:

if (ScanDirectionIsBackward(scandir))
{
reverse_sort = !index->reverse_sort[i];
nulls_first = !index->nulls_first[i];
}
else
{
reverse_sort = index->reverse_sort[i];
nulls_first = index->nulls_first[i];
}

/*
* OK, try to make a canonical pathkey for this sort key. Note we're
* underneath any outer joins, so nullable_relids should be NULL.
*/
cpathkey = make_pathkey_from_sortinfo(root,
indexkey,
NULL,
index->sortopfamily[i],
index->opcintype[i],
index->indexcollations[i],
reverse_sort,
nulls_first,
0,
index->rel->relids,
false);


I don't know how easy it would be to take nullability into account here, but it isn't done at the moment.

Perhaps you could suggest that to the pgsql-hackers mailing list.

StackExchange Database Administrators Q#254731, answer score: 6