consider supporting an `enable_approxscan` option

[cevian]

problem

When monitoring the recall of a vector query, you sometimes want to disable all approximate indexes to get the ground_truth and monitor recall.

Yet enable_indexscan isn't ideal since it turns off all indexes, causing problems in complex queries that make use of multiple tables/indexes.

solution

Introduce a new guc convention, that if enable_approxscan is set and marked as off, turns off approximate indexes (by setting the cost really high as other enable_* gucs do). This guc wouldn't be defined in pgvector, but pgvector would check if it's set (just by name) and act accordingly.

I propose enable_approxscan as the name but open to other suggestions.

I think this convention would ideally, in time apply to all approximate Postgres indexes, certainly we'd also implement it in pgvectorscale's diskann.

question

Curious about thoughts on whether or not this is a good idea.

[VoVAllen]

You can easily skip using index by ORDER by distance + 0, or manually set it's as unavailable through pg index status.

[jkatz]

manually set it's as unavailable through pg index status.

I would strongly advise against this method of disabling index scans as this may have unintended consequences around index maintenance.

[jkatz]

This guc wouldn't be defined in pgvector, but pgvector would check if it's set (just by name) and act accordingly.

If the plan is to propose it upstream, this is likely a discussion for pgsql-hackers, but scoping it to approximate indexes seems fairly limited. The "larger" feature would be to selectively enable/disable indexes from scanning, which is another way of saying "hints", which is a topic with a very long history. Alternatively, you can use an extension like pg_hint_plan to provide hints in queries to force avoiding using a particular index.

[VoVAllen]

https://stackoverflow.com/questions/6146024/is-it-possible-to-temporarily-disable-an-index-in-postgres
@jkatz I mean update pg_index set indisvalid = false where indexrelid = 'test_pkey'::regclass to alter the index status instead of disabling all the index scan.

[gwenshap]

Just to add another option: hypopg extension also allows for selective disabling of indexes.
Using a mature and well-tested extension is a better approach than directly updating catalog tables.

[jkatz]

https://stackoverflow.com/questions/6146024/is-it-possible-to-temporarily-disable-an-index-in-postgres @jkatz I mean update pg_index set indisvalid = false where indexrelid = 'test_pkey'::regclass to alter the index status instead of disabling all the index scan.

This is quoted from a StackOverflow post 14 years ago, and it's still not considered a safe operation (at least the last time I talked to PostgreSQL hackers who work on indexes on this topic). The mechanism for concurrent index building is different than the ongoing maintenance required for an index.

[jkatz]

Just to add another option: hypopg extension also allows for selective disabling of indexes. Using a mature and well-tested extension is a better approach than directly updating catalog tables.

@gwenshap Unless something has changed recently, hypopg doesn't let you explicitly disable an index, but rather shows the theoretical cost/performance of using an index.

[cevian]

@jkatz the GUC it wouldn't need to be define upstream. You can have have custom gucs named whatever you want (as long as they have a period). Thus, all we need is a convention: we can use plan.enable_approxscan or something. That doesn't need to be pre-define anywhere ahead of time.

[gwenshap]

@gwenshap Unless something has changed recently, hypopg doesn't let you explicitly disable an index, but rather shows the theoretical cost/performance of using an index.

@jkatz Good point. It lets you hypothetically hide a real index and see the change to the plan. But the index will still get used when executing the query (as seen with explain analyze)
For reference: https://hypopg.readthedocs.io/en/rel1_stable/usage.html#hypothetically-hide-existing-indexes

[ankane]

Hi @cevian, thanks for the suggestion. I've considered adding hnsw.enable_scan and ivfflat.enable_scan options to support this, but since there are already ways to do this (+ 0 to ORDER BY or pg_hint_plan), it's not something I'd like to add right now.