10

I've been reading this book which says that

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.

I recognize that the book is more than 10 years old, but it has already proven quite useful--Using instructions gleaned from its pages, I've sped a query up by a factor of ten.

Further, in running EXPLAIN ANALYZE on a SELECT query, I've found that none of my indexes are being used, even when by all rights, they ought to be.

Thus, my question is:

Supposing there is a table which has a column, whose column definition includes "NOT NULL", and that an index exists which covers this column, would this index be used in a query of that table where the columns is part of the query?

Like:

CREATE TABLE my_table(
a varchar NOT NULL
);

CREATE INDEX ix_my_table ON my_table(a);

SELECT a from my_table;
9

PostgreSQL certainly can use an index for IS NOT NULL. I don't see any query planner assumptions about that condition, either.

If the null fraction for the column (pg_statistic.stanullfrac) is low enough to suggest that the index is usefully selective for the query, PostgreSQL will use an index.

I can't figure out what you're trying to say with:

If this is correct, is my understanding that an index on a column defined as "NOT NULL" not be used in a query which uses that column?

Certainly an index won't get used for an IS NOT NULL condition on a NOT NULL column. It'd always match 100% of rows, so a seqscan will almost always be much faster.

PostgreSQL won't use an index if the index doesn't filter out a large proportion of rows for a query. The only likely exception is when you're asking for a set of columns covered by a single index, in an order matching that of the index. PostgreSQL might do an index-only scan then. E.g. if there's an index on t(a, b, c) and you:

select a, b FROM t ORDER BY a, b, c;

PostgreSQL might use your index, even though no rows are filtered out, because it only has to read the index and can skip reading the heap, avoid doing a sort, etc.

  • This is all true as of PG 9.0 – eradman Aug 11 '15 at 8:17
  • 1
    And even on a nullable column, a query with condition WHERE column IS NOT NULL may not use the index because, as the book says: "covers too large a range to be useful,". If 90% of the values are not null, a seqscan will probably be faster, too. – ypercubeᵀᴹ Aug 11 '15 at 9:25
  • Exactly. It might, but only if a large fraction of the table is null. Often in this case a partial index is a better choice anyway. – Craig Ringer Aug 11 '15 at 9:26
  • Yes. I was trying to say that (as I understand it) the part "covers too large a range" refers to the index but in regards to the specific condition and not the index in general. – ypercubeᵀᴹ Aug 11 '15 at 9:28
  • 2
    @FuriousFolder Heh, there are too many negations here. PostgreSQL won't use an index on a NOT NULL column for an IS NOT NULL query unless that index is also useful for other parts of the WHERE clause, join filters, etc, or is usable for an ordered index-only scan. In other words, it'll completely ignore the redundant IS NOT NULL on the NOT NULL column and make index use choices based on other details. (See edit, re index-only scans). – Craig Ringer Aug 12 '15 at 14:08
2

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;.

  • In the description of said book (emphasis mine): "Author Dan Tow outlines a timesaving method he's developed for finding the optimum execution plan--rapidly and systematically--regardless of the complexity of the SQL or the database platform being used" Also, perhaps you overlooked #1 of the question, namely, that the column is defined as NOT NULL, not that the query uses IS NOT NULL as its index condition. This is in the comments you referenced, but I'll update the question to include it. – FuriousFolder Aug 12 '15 at 17:51
  • Further, the book itself is language agnostic: the only DMBS-specific parts are about showing query plans, which Postgres makes quite simple :) – FuriousFolder Aug 12 '15 at 17:55
  • 1
    @FuriousFolder the column is defined as NOT NULL but this part (in your question, from the book): "that Indexed_Col IS NOT NULL covers ..." is referring to the where condition and not the column definition. Although it's hard to be sure, because it's out of context. Perhaps you should include the whole (preceding) paragraph from the book. – ypercubeᵀᴹ Aug 12 '15 at 18:03
-1

You haven't posted your query or example data. But the most common reason indexes are not used has to do with volume.

Indexes are like a phonebook that translates a column to a row location. If you're only looking for a few rows, it makes sense to look up each row in the phonebook, and then look up the row in the main table.

But for more than a few rows, it's cheaper to skip the phonebook, and iterate over all rows in the main table. In my experience the tipping point is around 100 rows.

  • "Indexes are like a phonebook that translates a column to a row location. If you're only looking for a few rows, it makes sense to look up each row in the phonebook, and then look up the row in the main table." Actually, indexes are like smaller phonebooks which are updated anytime the phonebook they index is updated. You know that any time you open a smaller phonebook, you're going to find any and all information that its indexing condition describes. E.g. All people named 'frank' on an index table: CREATE INDEX ix_frank ON people(name) WHERE name ='frank'. – FuriousFolder Aug 11 '15 at 21:12
  • This allows an index-only scan to be much much faster, since you can read the whole "smaller phonebook" into memory, which is not feasible with a multi-million lined table. – FuriousFolder Aug 11 '15 at 21:12
  • @FuriousFolder: You are describing an index-only scan. But the OP says that his indexes are not being used, which wouldn't happen if an index-only scan would satisfy the query. – Andomar Aug 12 '15 at 17:29
  • Andomar... I am the OP, haha. My goal is exactly that; to get this query to use an index-only scan. I have since achieved it, since Craig explained that postgres is able to use an index on a column where the column's definition includes NOT NULL – FuriousFolder Aug 12 '15 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.