While working with vector search and using a HSNW index I saw that sometimes I didn’t get back data but sometimes I would. I thought it could be because of the HSNW index needing to be updated.
As far as I know indexes are always updated when new data is inserted/updated/removed but I wanted to verify that
Certainly HNSW index (as well as any other indexes) is updated when new data is added or updated. Please explain what do you mean by “didn’t get back data”. Pleas notice, that HNSW implements approximate search - it means that returned result may be incomplete.
I think we had an issue where we had multiple indexes, so I think that might have been the problem.
Also one question, I read somewhere that when we want to use the index we need to do SET enable_seqscan = off. What does this exactly do?
Usually you have not to do it. Postgres should choose optimal execution plan without extra hints.
You can check query execution plan using
explain select * from ....
But sometimes due to lack of statistic or wrong estimation of selectivity, Postgres optimiser considers that sequential scan plan is more efficient than index scan. In such cases you can explicitly prohibit sequential plan by setting
enable_seqscan=off. Postger still use seqscan if not other choice is available.
Thank you very much! I think that answers my questions perfectly