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=<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 :)
account_id
and the gin index onname
alone.