Re:An unexpected find that freed 20GB of unused index space ...
by mnw21cam on Monday February 01, 2021 @10:13AM
(#25989307)
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.
Re:An unexpected find that freed 20GB of unused index space ...
by maweki on Monday February 01, 2021 @11:03AM
(#25989812)
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.
Re:An unexpected find that freed 20GB of unused index space ...
by mnw21cam on Monday February 01, 2021 @11:34AM
(#25990173)
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?
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.