| From: | Pantelis Theodosiou <ypercube(at)gmail(dot)com> |
|---|---|
| To: | Marko Tiikkaja <marko(at)joh(dot)to> |
| Cc: | pgsql-bugs(at)postgresql(dot)org |
| Subject: | Re: BUG #14676: neqsel is NULL dumb |
| Date: | 2017-05-29 17:08:34 |
| Message-ID: | CAE3TBxx0pjWQ1YyXHqj3zEop1FCcqd-0QKNdauECgEbvXoj0Vg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
On Mon, May 29, 2017 at 4:38 PM, <marko(at)joh(dot)to> wrote:
> 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.
>
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 ;
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)
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://stream.boxwatch.ru/mailpref/pgsql-bugs
>
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2017-05-29 18:53:29 | Re: Can't restore view with pg_restore |
| Previous Message | Tom Lane | 2017-05-29 16:52:19 | Re: BUG #14675: Perfomance Issue after Upgrading from 9.5.3 to 9.6.3 |