> You should just cross out that whole section. It's just flatly wrong.
>
> I had always assumed it was just people bringing assumptions over from
> Oracle where it is true. Perhaps this book is to blame for some of the
> confusion. Which book is it?
>
> Postgres indexes NULLs. It can use them for ORDER BY clauses.
I know this is an old-ish topic, but the question keeps coming up and I see
different answers every time.
I think I found the definitive answer and it looks like everyone (Bruce,
Tom, the book) is half-right. Maybe this should go in a FAQ or something
since there seems to be so much confusion.
From section 41.3 of the documentation - this section describes the pg_am
table:
> An index access method that supports multiple columns
> (has amcanmulticol true) must support indexing null
> values in columns after the first, because the planner
> will assume the index can be used for queries on just
> the first column(s). For example, consider an index
> on (a,b) and a query with WHERE a = 4. The system will
> assume the index can be used to scan for rows
> with a = 4, which is wrong if the index omits rows
> where b is null. It is, however, OK to omit rows
> where the first indexed column is null. (GiST
> currently does so.) amindexnulls should be set true
> only if the index access method indexes all rows,
> including arbitrary combinations of null values.
Here's what I get when I look at pg_am:
select amname, amcanmulticol, amindexnulls from pg_am;
amname | amcanmulticol | amindexnulls
--------+---------------+--------------
rtree | f | f
btree | t | t
hash | f | f
gist | t | f
So it looks like btree indexes will index completely-NULL values, but the
other types won't index a row where all of the index columns are NULL.
Am I reading that right?
It sounds like the explanation quoted from the book is correct for all types
except for btree?
