[BUGS] BUG #14807: Query Planner should ignore nulls last/first conditionfor 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@gmail.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;



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs