Re: [BUGS] BUG #14676: neqsel is NULL dumb
| От | Pantelis Theodosiou |
|---|---|
| Тема | Re: [BUGS] BUG #14676: neqsel is NULL dumb |
| Дата | |
| Msg-id | CAE3TBxx0pjWQ1YyXHqj3zEop1FCcqd-0QKNdauECgEbvXoj0Vg@mail.gmail.com обсуждение исходный текст |
| Ответ на | [BUGS] BUG #14676: neqsel is NULL dumb (marko@joh.to) |
| Ответы |
Re: [BUGS] BUG #14676: neqsel is NULL dumb
|
| Список | pgsql-bugs |
On Mon, May 29, 2017 at 4:38 PM, <marko@joh.to> wrote:
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.
Since you say that the majority of rows have NULL in nullable, I would try a partial index with: WHERE (nullable IS NOT NULL)
create table foo(nullable int, a text);
create index fff on foo(nullable, a) where nullable is not null ;
create table foo(nullable int, a text);
create index fff on foo(nullable, a) where nullable is not null ;
insert into foo --- 12K rows ;
explain analyze select nullable, a from foo where nullable <> 1 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on foo (cost=11.23..55.35 rows=11997 width=20) (actual time=0.040..0.048 rows=16 loops=1)
Recheck Cond: (nullable IS NOT NULL)
Filter: (nullable <> 1)
Rows Removed by Filter: 3
Heap Blocks: exact=3
-> Bitmap Index Scan on fff (cost=0.00..8.23 rows=19 width=0) (actual time=0.018..0.018 rows=19 loops=1)
Planning time: 0.117 ms
Execution time: 0.081 ms
(8 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on foo (cost=11.23..55.35 rows=11997 width=20) (actual time=0.040..0.048 rows=16 loops=1)
Recheck Cond: (nullable IS NOT NULL)
Filter: (nullable <> 1)
Rows Removed by Filter: 3
Heap Blocks: exact=3
-> Bitmap Index Scan on fff (cost=0.00..8.23 rows=19 width=0) (actual time=0.018..0.018 rows=19 loops=1)
Planning time: 0.117 ms
Execution time: 0.081 ms
(8 rows)
--
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 по дате отправления: