Re: Forcing index usage

Greetings,

* Michael Lewis (mlewis@entrata.com) wrote:
> > Is there a way to tell Postgres “please don’t use index X when queries
> > that could use index Y instead occur?”
>
> No. But you could re-write the query to make the date index useless. The
> simplest way that comes to mind is putting the query that does your
> full-text search in a CTE (WITH keyword, it is an optimization boundary)
> and then ordering and applying your limit to the materialized set that
> comes out of that. eg.
>
> WITH cte_full_text_results AS(
> SELECT date, result FROM big_a_table WHERE text = 'whatever'
> )
> SELECT * FROM cte_full_text_results ORDER BY date DESC limit 10;

Note that in v12, you'll need to include the MATERIALIZE keyword,
otherwise we'll in-line the CTE and you might get the plan you don't
like.  That said, it seems a bit unfortunate that there's no clear way
to create an index which specifically answers this query; figuring out a
way to do that could be very beneficial in a number of areas.  The RUM
index type attempts to improve things here, as I understand it.

Thanks!

Stephen