What PostgreSQL has over other open source SQL databases: Part II

Re:What PostgreSQL has over other open source SQL databases:... by Veratyr on Sunday October 25, 2015 @04:46PM (#10448341)

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.

[ Parent | on HN ]

Re:What PostgreSQL has over other open source SQL databases:... by b3n on Sunday October 25, 2015 @07:19PM (#10448862)

I 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.

[ Parent | on HN ]

Re:What PostgreSQL has over other open source SQL databases:... by Veratyr on Monday October 26, 2015 @12:00AM (#10449539)

Hmm, 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;

[ Parent | on HN ]