Re: Using index for IS NULL query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Index is not used for

is null

condition:

create index  makse_dokumnr_idx on makse(dokumnr);
explain select
    sum( summa)
  from MAKSE
  where  dokumnr is null

"Aggregate  (cost=131927.95..131927.96 rows=1 width=10)"
"  ->  Seq Scan on makse  (cost=0.00..131927.94 rows=1 width=10)"
"        Filter: (dokumnr IS NULL)"
>
>
Table makse contains 1200000 rows and about 800 rows with dokumnr is null so using index is much faster that seq scan.
How to fix ?

Yes, NULL values are not stored in the index, but you may create functional index on

(CASE WHEN dokumnr IS NULL THEN -1 ELSE dokumnr END)

and then use the same expression in the WHERE clause. You may replace the '-1' value by something that's not used in the dokumnr column.

regards
Tomas

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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux