Message-ID: <1543863089.8141.15.camel@lists.simkin.ca>
Subject: Re: Slow Bitmap Index Scan
From: Alan Hodgson <ahodgson@lists.simkin.ca>
To: "pgsql-performance@lists.postgresql.org"
	 <pgsql-performance@lists.postgresql.org>
Date: Mon, 03 Dec 2018 10:51:29 -0800
In-Reply-To: <EE225117-33A4-45E7-9D41-680D591AC5C9@motus.com>
References: <05111C93-69B4-4D5B-8AD3-8C9C47E0F01E@motus.com>
	 <20181128191753.GM30707@telsasoft.com>
	 <EE225117-33A4-45E7-9D41-680D591AC5C9@motus.com>
Content-Type: multipart/alternative; boundary="=-sCjPmgx5Pz+Oq4e1/lJZ"
Mime-Version: 1.0
Precedence: bulk


--=-sCjPmgx5Pz+Oq4e1/lJZ
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

On Mon, 2018-12-03 at 18:41 +0000, Scott Rankin wrote:
> Upon further analysis, this is - unsurprisingly - taking place when we have multiple prefixed search terms in a ts_query going against a tsvector index.
> 
> We have roughly 30 million rows in the table, and the search column is basically a concatenation of a location's name (think "Walmart #123456") and its street address.
> 
> We use these searches mostly for autocompleting of a location search.  So the search for that record above might be "Walmart 123", which we change to be to_tsquery('walmart:* &123:*').  We prefix both terms to correct for misspellings or lazy typing.
> 
> Is it unrealistic to think that we could have sub-1000ms searches against that size of a table?
> 

We've found trigram indexes to be much faster and more useful for these
types of searches than full-text.

https://www.postgresql.org/docs/10/pgtrgm.html

Might be worth a try, if you haven't tested them before.
--=-sCjPmgx5Pz+Oq4e1/lJZ
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit

<html><head></head><body><div>On Mon, 2018-12-03 at 18:41 +0000, Scott Rankin wrote:</div><blockquote type="cite" style="margin:0 0 0 .8ex; border-left:2px #729fcf solid;padding-left:1ex"><pre>Upon further analysis, this is - unsurprisingly - taking place when we have multiple prefixed search terms in a ts_query going against a tsvector index.

We have roughly 30 million rows in the table, and the search column is basically a concatenation of a location's name (think "Walmart #123456") and its street address.

We use these searches mostly for autocompleting of a location search.  So the search for that record above might be "Walmart 123", which we change to be to_tsquery('walmart:* &amp;123:*').  We prefix both terms to correct for misspellings or lazy typing.

Is it unrealistic to think that we could have sub-1000ms searches against that size of a table?

</pre></blockquote><div><br></div><div>We've found trigram indexes to be much faster and more useful for these types of searches than full-text.</div><div><br></div><div><a href="https://www.postgresql.org/docs/10/pgtrgm.html">https://www.postgresql.org/docs/10/pgtrgm.html</a></div><div><br></div><div>Might be worth a try, if you haven't tested them before.</div></body></html>
--=-sCjPmgx5Pz+Oq4e1/lJZ--