On Wed, Apr 3, 2019 at 12:13 PM Jonathan Marks <jonathanaverymarks@xxxxxxxxx> wrote:
Is there a way to tell Postgres “please don’t use index X when queries that could use index Y instead occur?”
Late to the party here, but...
Not directly. I've had luck in changing the procost of functions (or the functions which back the operators) which are frequently executed in the slower plan, but not frequently executed in the faster plan. For example, walking the time index executing @@ on each row until it finds enough is probably going to involve a lot more @@ than using the full text index and invoking @@ only on the recheck rows. The cost of @@ (via "ts_match_vq") is probably way too low, especially if it has to fish an argument out of TOAST.
If that doesn't work, you can just change the query to prevent the bad index from being used.
For example, PostgreSQL is not currently smart enough to use an index on "mtime" to support the ordering of a query that is written as:
ORDER BY mtime + interval '0 seconds'
Cheers,
Jeff
