>> 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. > > Now I'm confused...
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? ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
