[lucian1900]

Partial indexes can be useful in any case where one value has much higher cardinality than others.

Indexing boolean columns is often only useful if one of the values is uncommon and the index is partial to only include those uncommon rows.

[mnw21cam]

Agreed. To explain why this is the case, consider that table in the story that had 99% NULL values. If you were to try to run "SELECT FROM table WHERE column IS NULL", then Postgresql wouldn't use the index anyway, because it would be faster to just read sequentially through the entire table and filter out the 1% that don't match.

[maweki]

That would highly depend on what you select. If the query could be answered by index only, like COUNT(*), it would probably use the index. You are right if you want to query any data from that row that's not in the index.

[mnw21cam]

I might be out of touch a little with Postgres (I last used it in 2010), but my impression was that COUNT(*) still needed to scan the actual table in order to exclude rows that had been deleted in a transaction, due to the way multi-version concurrency worked. Is this something that has been improved since then?

[zeroimpl]

They support index-only scans now, so there is some sort of optimization which bypasses the table lookup, at least in certain cases.