Denis Burke wrote: > Your suggestion to make it a partial index makes sense. When would > you NOT want to add the "where [indexedColumn] IS NOT NULL"? Seems > like it would always be helpful.
If the database cannot prove that a query will never search for a NULL value, it cannot use that index. However, 'normal' filters or joins with "a = b" indeed do not match NULLs. When in doubt, use EXPLAIN QUERY PLAN on your important queries. The documentation says: | When used judiciously, partial indexes can result in smaller database | files and improvements in both query and write performance. Here, "judiciously" means "the filter omits so many rows that it makes a difference". Regards, Clemens
