From: Alan Hodgson <ahodgson@lists.simkin.ca>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Slow Bitmap Index Scan
Date: Mon, 03 Dec 2018 10:51:29 -0800
Message-ID: <1543863089.8141.15.camel@lists.simkin.ca> (raw)
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>
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.
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-performance@postgresql.org
Cc: ahodgson@lists.simkin.ca, pgsql-performance@lists.postgresql.org
Subject: Re: Slow Bitmap Index Scan
In-Reply-To: <1543863089.8141.15.camel@lists.simkin.ca>
* 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