Re: Forcing index usage

On Wed, Apr 3, 2019 at 12:13 PM Jonathan Marks <jonathanaverymarks(at)gmail(dot)com>
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