Thread: [BUGS] BUG #14807: Query Planner should ignore nulls last/first conditionfor not-null fields in btree index [order by]
[BUGS] BUG #14807: Query Planner should ignore nulls last/first conditionfor not-null fields in btree index [order by]
From
serovov@gmail.com
Date:
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