4 ms·
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
by lucian1900 6y ago
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 6y agoAgreed. 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 6y agoThat 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 6y agoI 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 6y agoThey support index-only scans now, so there is some sort of optimization which bypasses the table lookup, at least in certain cases.