Apache Pinot

2nd question: if i plan to use lookup table for joins, i can only use it for decorating the query results - if i use lookup joins in the WHERE clause, queries will be very slow because the lookup join cannot benefit from indexing. is my understanding correct here?

```SELECT
  f.uuid,
  lookUp('dimTable', 'name', 'uuid', f.uuid) as name,
  lookUp('dimTable', 'country', 'uuid', f.uuid) as country,
  abs(sum(m.metric)) as sum_metric
FROM
  factTable f 
WHERE 
  f.event_time &gt; CAST(to_unixtime(date_trunc('day', now())) AS BIGINT)
  AND f.status = 'OPEN' 
  AND lookUp('dimTable', 'country', 'uuid', f.uuid) = 'SINGAPORE'
GROUP BY
  1,
  2,
  3
ORDER BY
  2```

```AND f.uuid IN (select uuid from dimTable where country='SINGAPORE')```

the only limitation here is the size of dim tables since they will be replicated in every server right?

whats the difference internally if i use lookup vs subquery in the above query?

ok, so if say i have:
```# factTable
-----------------
userid | raw_cell

# dimTable1
-----------------
raw_cell | mapped_cell

# dimTable2
-----------------
userid | attr1 | attr2```
i can do a query like
```SELECT 
  userid, 
  count(*) 
FROM 
  factTable f
WHERE 
  f.raw_cell IN (select raw_cell, mapped_cell FROM dimTable1 WHERE IN_ID_SET(raw_cell, '...') = 1) AND
  f.userid IN (select userid FROM dimTable2 WHERE attr1=val1 and attr2=val2)
GROUP BY
  userid```
?

can i use multiple dimension tables in a single query?

btw, instead of
```AND f.uuid IN (select uuid from dimTable where country='SINGAPORE')```
do you mean
```AND IN_SUBQUERY(uuid, 'select uuid from dimTable where country="SINGAPORE"')```
?

sorry, which of my questions are you referring to?

I think you can use multiple dim tables in single query 

I’ll test it later when i have access to my laptop

~<@UDT7GFEG6>~ ~<@UDQU92KBK> do you know if i can use dimension tables in subquery this way? I was under the impression that subquery can only be used with the same table in a query~

sorry checked <https://docs.google.com/document/d/1s6DZ9eTPqH7vaKQlPjKiWb_OBC3hkkEGICIzcd5gozc/edit#heading=h.axhmfzc3hrwa>:
&gt; Because the subquery is solved as a separate query, the subquery does not necessarily hit the same table as the main query. It can retrieve the IdSet from any table served by the broker.


<@UDRJ7G85T>  Can I confirm that using a sub query with an dimension table with the right indexes will not incur any performance hit?

That’s right.  You can try it yourself and compare the perf 

unfortunately it seems like there’s a perf hit with lookup join:
<https://apache-pinot.slack.com/archives/C011C9JHN7R/p1641547157041000?thread_ts=1641470475.007000&amp;cid=C011C9JHN7R>