From: | Alan Hodgson <ahodgson(at)lists(dot)simkin(dot)ca> |
---|---|
To: | "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Slow Bitmap Index Scan |
Date: | 2018-12-03 18:51:29 |
Message-ID: | 1543863089.8141.15.camel@lists.simkin.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2018-12-04 21:43:31 | Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0 |
Previous Message | Scott Rankin | 2018-12-03 18:41:54 | Re: Slow Bitmap Index Scan |