Greetings, * Michael Lewis (mle...@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
signature.asc
Description: PGP signature
