Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hBksC-0004mP-Hg for pgsql-general@arkaria.postgresql.org; Wed, 03 Apr 2019 18:44:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hBksB-0006m1-5c for pgsql-general@arkaria.postgresql.org; Wed, 03 Apr 2019 18:44:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hBksA-0006eo-Ky for pgsql-general@lists.postgresql.org; Wed, 03 Apr 2019 18:44:30 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hBks7-0008WA-RL for pgsql-general@postgresql.org; Wed, 03 Apr 2019 18:44:29 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id D863B5F7A5; Wed, 3 Apr 2019 14:44:25 -0400 (EDT) Date: Wed, 3 Apr 2019 14:44:25 -0400 From: Stephen Frost To: Michael Lewis Cc: Jonathan Marks , pgsql-general Subject: Re: Forcing index usage Message-ID: <20190403184425.GG6197@tamriel.snowman.net> References: <679378F9-E085-4FE3-BC78-3073A4B86438@gmail.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="ayJsc3xFE0sbxmbp" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --ayJsc3xFE0sbxmbp Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Michael Lewis (mlewis@entrata.com) wrote: > > Is there a way to tell Postgres =E2=80=9Cplease don=E2=80=99t use index= X when queries > > that could use index Y instead occur?=E2=80=9D >=20 > 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. >=20 > WITH cte_full_text_results AS( > SELECT date, result FROM big_a_table WHERE text =3D '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 --ayJsc3xFE0sbxmbp Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJcpP8JAAoJEO1sijiDR2RVIlcQAIJ+CckBuUcvpfQodZjAwzfn l0A7iXdgEXbVQwr5eIh19bhk+rAa3z373yEtezQgEMSxQW8qoglUUj/4GGBJW81Q puIXyvV2PIoed9MnMozgytxdhgGG8gQjcRwyhQy32RxibwlwoUgbOZqL3Xq8r5R9 aodv/kql/hbGkHxu6EtCm6IevvX1+wpSZyM/QTnMIMrEExtniEbEw+JFrl+vS5z6 xdqa+ggYG+nbBNA3cbD7v7PJjOlmoP+ZvTcbIfqMWqTrk5znuCqKemqcA01i7wD8 SwhJMougPzRNWEIwqXfh1xOtYQxswXavAQJzFWtPHyfrTMaML0mwQ/4c8nGfjEvB 1d/On1wl4NoisRMLZ9kFvZ7pknWYu6i62qmyfKWEQ+RIu77C72tl/g2C4xuqDvXS 3xH55RPrK3VmyJ/3KOcMSTX5vcGLL8JVMl37/ufUXM11kO/BAcZD0CuJAdKLyOoo XBVf+qUEdZwGJyAv3elGKorHaWo7/NmMXMB6K7x+2kpg3VfUmSd5zEpt4TFyxHe0 lGQTAx59Io8QdMOTPZz+n/eL5jSkhz3Plc7EoIzgpIFda1FODmgvO/m8DopGtHby CuYbgi6pXp8po4Q0uuxx5r5csJ38V/dsy6nuFzLl/7StYP7de8YYoNuZn63UJIxj CEtCMYweGG/ENp6ilH5m =1Hr9 -----END PGP SIGNATURE----- --ayJsc3xFE0sbxmbp--