Sample Header Ad - 728x90

Postgres - Improving multi-column GIN text search performance

0 votes
1 answer
322 views
I've got a schema for customer records a bit like:
account_id - UUID
name - text
I currently have a GIN index that looks like:
... USING GIN (account_id, name gin_trgm_ops)
We have a mix of accounts. Some have a lot of customers (10m+) and some only have a few (10k+). We have a lot of queries that look like:
SELECT from customers WHERE account_id= AND 
(
  name LIKE 'Bob%'
  OR name LIKE 'Alice%'
  OR name LIKE 'Dave%'
  OR name LIKE 'Carol%'
  OR name LIKE 'Edward%'
  OR name LIKE 'Fay%'
)
For large customers the index performance is good (queries ~2s). For small customers the index performance is comparatively poor (also ~2s). This matters due to how often we run these queries. Is there a way to improve this for smaller customers? We've noticed that replacing the index with a simple btree index on account_id is faster - scanning all the records for a single **small** account is faster than doing the index bitmap work across *all* accounts. Obviously, this is a lot slower on the large accounts. I think partitioning the table is the only way forward here. However, I'm hoping someone has a bright idea :)
Asked by ColinHowe (101 rep)
Jun 29, 2024, 10:56 AM
Last activity: Sep 20, 2025, 01:02 AM