Faster relevance ranking didn’t make it into PostgreSQL 9.4

Alas, the one big feature we really needed, the patch apparently got rejected.

PostgreSQL has a nice little full-text search story, especially when you combine it with other parts of our story (security-aware filtering of results, transactional integrity, etc.) Searches are very, very fast.

However, the next step — ranking the results — isn’t so fast. It requires a table scan (likely to TOAST files, meaning read a file and gunzip its contents) on every row that matched.

In our case, we’re doing prefix searches, and lots and lots of rows match. Lots. And the performance is, well, horrible. Oleg and friends had a super-fast speedup for this ready for PostgreSQL 9.4, but it apparently got rejected.

So we’re stuck. It’s too big a transition to switch to ElasticSearch or something. The customer probably should bail on prefix searching (autocomplete) but they won’t. We have an idea for doing this the right way (convert prefixes to candidate full words, as Google does, using PG’s built-in lexeme tools) but that is also too much for budget. Finally, we don’t have the option to throw SSDs at it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: