BUG #14807: Query Planner should ignore nulls last/first condition for not-null fields in btree index [order by]

The following bug has been logged on the website:

Bug reference: 14807
Logged by: Oleg Serov
Email address: serovov(at)gmail(dot)com
PostgreSQL version: 9.6.5
Operating system: Ubuntu
Description:

Query Planner should ignore null last/first condition for not-null fields in
btree index.

- I have a table with a field that is not null.
- I have an index on that field.
- I make a query 'ORDER BY field ASC'
- The planner does not use btree-index because default behavior for
postgresql order-by is NULLS FIRST, but for btree-indexes NULLS LAST.
- However, PostgreSQL knows that the field is not null. But it still does
not use the index.

PostgreSQL should ignore nulls last/nulls first if the field has not null
condition.

Here is the query to reproduce:

ROLLBACK; BEGIN;

CREATE TABLE btree_bug (
id BIGSERIAL,
rand BIGINT NOT NULL
);

INSERT INTO btree_bug
SELECT i, random() * 100000::BIGINT
FROM generate_series(0, 10000) AS i;

CREATE INDEX ON btree_bug USING btree(rand);

SELECT 'Uses index:';
EXPLAIN ANALYZE SELECT id FROM btree_bug ORDER BY rand DESC NULLS FIRST
LIMIT 10;
EXPLAIN ANALYZE SELECT id FROM btree_bug ORDER BY rand ASC NULLS LAST LIMIT
10;

SELECT 'Does not use index:';
EXPLAIN ANALYZE SELECT id FROM btree_bug ORDER BY rand DESC NULLS LAST LIMIT
10;
EXPLAIN ANALYZE SELECT id FROM btree_bug ORDER BY rand ASC NULLS FIRST LIMIT
10;