[openstreetmap/openstreetmap-website] Split text search of notes and note comments in two (PR #6998)

[Tom Hughes]

Postgres isn't able to figure out how to optimise the text search using the GIN indexes when presented with an OR that needs to search both tables but if we split the query in two ourselves and merge them back together with a union then it can use the indexes which can speed things up by two orders of magnitude.

Specifically testing with a query derived from real searches that hit use today I saw a reduction from 12000ms to 30ms or so which is around 400 times less, and that was testing on a slave that wasn't doing anything else.
You can view, comment on, or merge this pull request online at:

  https://github.com/openstreetmap/openstreetmap-website/pull/6998

-- Commit Summary --

  * Split text search of notes and note comments in two

-- File Changes --

    M app/controllers/api/notes_controller.rb (5)

-- Patch Links --

https://github.com/openstreetmap/openstreetmap-website/pull/6998.patchhttps://github.com/openstreetmap/openstreetmap-website/pull/6998.diff
-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/pull/6998
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/openstreetmap-website/pull/6998 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20260413/97c4d789/attachment.htm>