Re: index question

unsubscribe

I have heard that postgres will not use an index unless the field has a not null constraint on it. Is that true?

Rick Gigger wrote:
> I have heard that postgres will not use an index unless the
> field has a not null constraint on it. Is that true?

To be specific, we do not do index NULL values in a column, but we
easily index non-null values in the column.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Rick Gigger wrote:

> I have heard that postgres will not use an index unless the field has a
> not null constraint on it. Is that true?

Certainly not. PostgreSQL reserves the right not to use an index, for
example if it thinks that most of the table will satisfy the condition
anyway (for example if a table has keys in the range from 1 to 1,000,000
and you query for > 1,000 ... using the index doesn't make sense). But
claiming it will not use one if the field is allowed to contain NULL is
plainly wrong.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Rick Gigger wrote:

> I have heard that postgres will not use an index unless the field has
> a not null constraint on it. Is that true?

I have never heard that. There are some oddities with using an Index.
For example,
if you are using a bigint you need to '' the value or if you are using
an aggregrate function (mix/max)
you have to specify a where clause but I have not heard the one you have
mentioned.

Anybody else?

Sincerely,

Joshua Drake

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:

> Rick Gigger wrote:
> > I have heard that postgres will not use an index unless the
> > field has a not null constraint on it. Is that true?
>
> To be specific, we do not do index NULL values in a column, but we
> easily index non-null values in the column.

I don't think that's true. Postgres does index null values by default. Perhaps
you're thinking of Oracle which doesn't. You can get Oracle's behaviour in
Postgres by using a partial index "WHERE col IS NOT NULL".

The following would not be able to use the index scan plan that it does if
NULL values weren't indexed:

db=> create table test (i integer);
CREATE TABLE
db=> create index i on test(i);
CREATE INDEX
db=> set enable_seqscan = off;
SET
db=> explain select * from test order by i;
QUERY PLAN
------------------------------------------------------------------
Index Scan using i on test (cost=0.00..24.00 rows=1000 width=4)
(1 row)

Perhaps the poster is thinking of the fact that postgres doesn't consider "IS
NULL" and "IS NOT NULL" to be indexable operations. So for example things like
this cannot use an index:

db=> explain select * from test where i is not null;
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on test (cost=100000000.00..100000020.00 rows=995 width=4)
Filter: (i IS NOT NULL)
(2 rows)

db=> explain select * from test where i is null;
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on test (cost=100000000.00..100000020.00 rows=6 width=4)
Filter: (i IS NULL)
(2 rows)

That's a bit of a deficiency but that too can be addressed by using a partial
index:

db=> create index ii on test(i) where i is not null;
CREATE INDEX
db=> explain select * from test where i is not null;
QUERY PLAN
------------------------------------------------------------------
Index Scan using ii on test (cost=0.00..23.95 rows=995 width=4)
Filter: (i IS NOT NULL)
(2 rows)

Though the added cost of maintaining another index is not really a good
tradeoff. This is only really a good idea if the partial index covers a small
subset of the total number of records, or if it is indexing a column not
already indexed.

You might also reconsider whether using NULL in the data model is right,
usually it's worth avoiding except in the case of truly "unknown" values.

--
greg