From: Stephen Frost <sfrost@snowman.net>
To: Michael Lewis <mlewis@entrata.com>
Cc: Jonathan Marks <jonathanaverymarks@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Forcing index usage
Date: Wed, 3 Apr 2019 14:44:25 -0400
Message-ID: <20190403184425.GG6197@tamriel.snowman.net> (raw)
In-Reply-To: <CAHOFxGrN659igBLGUh+DMvsrRiqBkO+Lr9sv1HSA81Y+YSmZCQ@mail.gmail.com>
References: <679378F9-E085-4FE3-BC78-3073A4B86438@gmail.com>
<CAHOFxGrN659igBLGUh+DMvsrRiqBkO+Lr9sv1HSA81Y+YSmZCQ@mail.gmail.com>
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
Attachments:
[application/pgp-signature] signature.asc (818B, ../20190403184425.GG6197@tamriel.snowman.net/2-signature.asc)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: sfrost@snowman.net, mlewis@entrata.com, jonathanaverymarks@gmail.com
Subject: Re: Forcing index usage
In-Reply-To: <20190403184425.GG6197@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox