From: Jonathan Marks <jonathanaverymarks@gmail.com>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 12.2 \(3445.102.3\))
Subject: Forcing index usage
Message-Id: <679378F9-E085-4FE3-BC78-3073A4B86438@gmail.com>
Date: Wed, 3 Apr 2019 12:13:22 -0400
To: pgsql-general@postgresql.org
Precedence: bulk

Hi folks =E2=80=94

We=E2=80=99ve got several tables that are relatively large (~250-500GB =
in total size) that use very large (tens to hundreds of GB) GIN indexes =
for full-text search. We=E2=80=99ve set the column statistics for our =
tsvector columns as high as they go (10,000). We almost always order our =
search results by a separate date column (which has an index) and we =
almost always use a limit.

Whenever the query planner chooses to use the indexes, queries on these =
tables are somewhat fast, maxing out at a few hundred milliseconds per =
query (which is terrible, but acceptable to end users).

When the query planner chooses not to use the indexes, queries can take =
many tens of seconds if they ever finish at all. When this happens, the =
query planner usually chooses to use the date index instead of the GIN =
index, and that is almost always a bad idea. We have sometimes been able =
to trick it into a better query plan by also adding the tsvector column =
in the ORDER BY clause, but that has bad performance implications if the =
result set is large.

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

Thank you!=