4 ms·
I needed to implement tagging a while ago, particularly looking up a row with multiple tags. As far as I know, the main way to do this is usually to put the tag
by Veratyr 11y ago
I needed to implement tagging a while ago, particularly looking up a row with multiple tags. As far as I know, the main way to do this is usually to put the tags in another table and join multiple times (once per tag) to find a row that has them all. This performed rather badly (100s of ms for 5 tags from memory).
But with Postgres, you can put a GIN index on an Array column. So I moved my tags into an array column and suddenly querying for every one of several million rows that had matched a set of 7 tags took ~10ms.
Postgres is awesome.
- b3n 11y agoI don't know much about SQL, but are you sure you need multiple joins for that? I'd do something like this (psuedocode) on the table with the tags: SELECT foreign-id, COUNT(*) FROM Tags WHERE tag IN ('foo', 'bar', 'baz') GROUP BY foreign-id HAVING COUNT(*) = 3 It does seem rather hacky though.
- Veratyr 11y agoHmm, I don't know much about it either and it looks like you're right. I don't believe that querying it is nearly as fast as querying a GIN index (or as easy) still. With Postgres I just do this and it gives me a result in a couple ms: SELECT * FROM things WHERE ARRAY['tag1', 'tag2'] <@ tags;