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.
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 |