Hi, I have a new customer who is moving from MongoDB and wishes to implement similar queries in Vertica. Our problem is: given a set of parameters selected, return all possible item ID's that reference all parameters in a boolean expression. The table parameterValues is tuples of (uniquePn, parameterValueId). Each uniquePn has multiple parameters. Here is our query so far for 60399 AND 11 AND 41 AND (5838 OR 5839):
select parameterValues.uniquePn from intellidata.parameterValues
INNER JOIN upFilter USING (uniquePn)
WHERE parameterValueId IN (60399, 11, 41, 5838)
GROUP BY parameterValues.uniquePn HAVING COUNT(parameterValues.uniquePn) = 4
UNION ALL
select parameterValues.uniquePn from intellidata.parameterValues
INNER JOIN upFilter USING (uniquePn)
WHERE parameterValueId IN (60399, 11, 41, 5839)
GROUP BY parameterValues.uniquePn HAVING COUNT(parameterValues.uniquePn) = 4
Projections ordered by paramId and uniquePn were created. Vertica can run it, but it's not considered fast enough (~3-5 seconds per request, often needs multiple requests) to support a front end. Any thoughts how we can further optimize? I suspect this is a common parametric use case, but googling didn't turn up a faster way than above.
