Comparison
CardinalityvsQuery planner
Cardinality
you index the boolean column, the planner ignores it, and the reason is that half the table matches any value you look up.
How many distinct values a column holds, and by extension how much an index on it narrows the search. High-cardinality columns make good indexes; low-cardinality ones usually do not, because reading the index plus random rows costs more than a scan. This is why the planner declines indexes you were sure would help.
Full entry →Query planner
you run EXPLAIN and find out the database chose a sequential scan over the index you added last week.
The component that turns your declarative query into a physical plan, choosing join order, access methods and algorithms based on table statistics. It is only as good as those statistics, which is why a plan can flip catastrophically after a bulk load and before the next ANALYZE. Reading plans is the single highest-leverage database skill; guessing at query performance without one is superstition.
Full entry →