In addition to Craig's thorough answer, I wanted to add that the cover of the book you reference says:
Covers Oracle, DB2 & SQL Server
So I wouldn't trust it to be a great source of advice on PostgreSQL in particular. Every RDBMS can be surprisingly different!
I'm a little confused about your original question, but here's an example showing that section of the book is not 100% correct. To avoid further confusion, here's the whole relevant paragraph, you can see it in Google Book Search.
The database assumes that Indexed_Col IS NOT NULL covers too large a range to be useful, so the database will not drive to an index from this condition. In rare cases, having any nonnull value is so rare that an index range scan over all possible nonnull values is beneficial. In such cases, if you can figure out a safe lower or upper limit to the range of all possible values, you can enable a range scan with a condition such as Positive_ID_Column > -1 or Date_Column > TO_DATE('0001/01/01', 'YYYY/MM/DD').
Postgres can actually (in the following contrived case) use an index to satisfy IS NOT NULL
queries without adding range scan kludges like the suggested Positive_ID_Column > -1
. See the comments on Craig's questions for why Postgres is choosing this index in this particular case, and the note about using partial indexes.
CREATE TABLE bar (a int);
INSERT INTO bar (a) SELECT NULL FROM generate_series(1,1000000);
INSERT INTO bar (a) VALUES (1);
CREATE INDEX bar_idx ON bar (a);
EXPLAIN ANALYZE SELECT * FROM bar WHERE a IS NOT NULL;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Index Only Scan using bar_idx on bar (cost=0.42..8.44 rows=1 width=4) (actual time=0.094..0.095 rows=1 loops=1)
Index Cond: (a IS NOT NULL)
Heap Fetches: 1
Total runtime: 0.126 ms
(4 rows)
This is Postgres 9.3 by the way, but I believe the results would be roughly similar on 9.1, although it wouldn't use an "Index Only Scan".
Edit: I see you've clarified your original question, and you are apparently wondering why Postgres isn't using an index in a simple example like:
CREATE TABLE my_table(
a varchar NOT NULL
);
CREATE INDEX ix_my_table ON my_table(a);
SELECT a from my_table;
Probably because you don't have any rows in the table. So add some test data and ANALYZE my_table;
.