We saved $50k/year with a Go microservice coded in a hackathon

Re:We saved $50k/year with a Go microservice coded in a hack... by orf on Tuesday April 10, 2018 @09:16PM (#16807253)

Hmm, that does seem probable. In fact that could make the SQL even more efficient as you'd only need a combined index on the 'prop' and 'value' columns, rather than N arbitrary combinations of indexes that may or may not be used.

Edit: Had some bad attempt at writing this query but it's rather late and it made no sense.

[ Parent | on HN ]

Re:We saved $50k/year with a Go microservice coded in a hack... by any626 on Tuesday April 10, 2018 @09:20PM (#16807273)

You would need to have a new join for each new property

    SELECT DISTINCT loyaltyMemberID
    from members as m
    INNER JOIN properties as p1 on m.id = p1.user_id
    INNER JOIN properties as p2 on m.id = p2.user_id
    INNER JOIN properties as p3 on m.id = p3.user_id
    AND (p1.prop = 'gender' AND p1.value = x)
    AND ((p2.prop = 'age' AND p2.value = y) OR (p3.prop = 'censor' AND p3.value = z))

[ Parent | on HN ]

Re:We saved $50k/year with a Go microservice coded in a hack... by TimJYoung on Tuesday April 10, 2018 @10:45PM (#16807641)

There's no need for the extra joins, you can just do the one join and then filter everything in the WHERE clause:

  SELECT DISTINCT loyaltyMemberID
  from members as m
  INNER JOIN properties as p on m.id=p.user_id
  WHERE (p.prop='name' AND p.value = value) AND

  ...etc.

[ Parent | on HN ]

Re:We saved $50k/year with a Go microservice coded in a hack... by 83457 on Wednesday April 11, 2018 @09:29AM (#16810859)

But how would you do exclusions with your approach?

[ Parent | on HN ]

Re:We saved $50k/year with a Go microservice coded in a hack... by TimJYoung on Thursday April 12, 2018 @05:11AM (#16819177)

I'm not sure what you're asking - could you give me an example of what you're envisioning that couldn't be satisfied with a combination of Boolean expressions in the WHERE clause ?

[ Parent | on HN ]

Re:We saved $50k/year with a Go microservice coded in a hack... by 83457 on Thursday April 19, 2018 @01:51PM (#16878198)

With this query approach how do you find people that have a prop1 but do not a prop2?

If you get records back with prop1 then you have to remove those records from results based on another record.

There are multiple ways to accomplish this but it can't be done with a single join and simple where clause.

[ Parent | on HN ]

Re:We saved $50k/year with a Go microservice coded in a hack... by TimJYoung on Tuesday May 01, 2018 @08:42PM (#16973140)

Sorry, I missed your reply. Yes, you are correct, in that case you would need to use a except, sub-query, derived table, etc.

[ Parent | on HN ]

Re:We saved $50k/year with a Go microservice coded in a hack... by pbnjay on Tuesday April 10, 2018 @10:11PM (#16807510)

Especially with partial indexes, I still feel like this structure will be significantly faster than the original UNION ALL ... GROUP BY on calculated fields.

And they mention in the post that most queries don't use that many fields.

[ Parent | on HN ]