BUG #14676: neqsel is NULL dumb

From: marko(at)joh(dot)to
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14676: neqsel is NULL dumb
Date: 2017-05-29 15:38:47
Message-ID: 20170529153847.4275.95416@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14676
Logged by: Marko Tiikkaja
Email address: marko(at)joh(dot)to
PostgreSQL version: 9.6.3
Operating system: Linux
Description:

I'm having an issue with a case where a column is mostly NULLs and I'm doing
an inequality query on the column:

=# create table foo(nullable int);
CREATE TABLE

=# insert into foo select case when i = 1 then i else null end from
generate_series(1, 1000) gs(i);
INSERT 0 1000

=# analyze foo;
ANALYZE

=# explain select * from foo where nullable <> 1;
QUERY PLAN
------------------------------------------------------
Seq Scan on foo (cost=0.00..16.50 rows=999 width=4)
Filter: (nullable <> 1)
(2 rows)

This seems to be because neqsel() doesn't take at all into account that both
operators will exclude NULL rows, and does a simple 1.0 - eqsel(). This
also means that a partial index such as:

create index on foo(othercolumn) where nullable <> 1

will never be used.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Daniele Varrazzo 2017-05-29 16:38:59 Fixed PL/Python hint about array of composites
Previous Message walpino 2017-05-29 14:46:58 BUG #14675: Perfomance Issue after Upgrading from 9.5.3 to 9.6.3