3 ms·
Potentially, there was an index which the author knew would yield good performance for a particular query, but the query planner over-estimated the number of ro
by radiowave 10y ago
Potentially, there was an index which the author knew would yield good performance for a particular query, but the query planner over-estimated the number of rows, and so decided that a sequential scan would be a better choice. By executing the command, "SET enable_seqscan = OFF;" inside a session, you can force postgres to use an index if it possibly can.
These sort of problems can occur (for example) when very complicated joins are performed, or when unstructured data is being used as part of a join, as these tend to lead to poor estimates of the number of rows that the different parts of the query will yeild.
Relevant docs here:
https://www.postgresql.org/docs/current/static/runtime-config-query.html https://www.postgresql.org/docs/current/static/runtime-confi...
- Groxx 10y agoFor additional anecdotes, I've had to hint MySQL's planner twice, both times to exclude an index. It'd switch because the cardinality of one index was much better, but there were enough pathological cases (and they were the most-often used) that the planner's choices were proving fatal. It's not too abnormal. Sometimes you know the structure of your data better than the database's internal summary does, so you just give it a nudge in the right direction.
- williamstein 10y agoHere's exactly what happened: https://gist.github.com/williamstein/fb31e07d4057232bd3a3e780d6220482 https://gist.github.com/williamstein/fb31e07d4057232bd3a3e78...