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