[BUGS] BUG #14676: neqsel is NULL dumb - Mailing list pgsql-bugs

From marko@joh.to
Subject [BUGS] BUG #14676: neqsel is NULL dumb
Date
Msg-id 20170529153847.4275.95416@wrigleys.postgresql.org
Whole thread Raw
Responses Re: [BUGS] BUG #14676: neqsel is NULL dumb
Re: [BUGS] BUG #14676: neqsel is NULL dumb
List pgsql-bugs
The following bug has been logged on the website:

Bug reference:      14676
Logged by:          Marko Tiikkaja
Email address:      marko@joh.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.


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

pgsql-bugs by date:

Previous
From: walpino@gmx.ch
Date:
Subject: [BUGS] BUG #14675: Perfomance Issue after Upgrading from 9.5.3 to 9.6.3
Next
From: Daniele Varrazzo
Date:
Subject: [BUGS] Fixed PL/Python hint about array of composites