Hi, I'd like some guidance on performing large joi...
# troubleshooting
c
Hi, I'd like some guidance on performing large joins. 1. I ran into some issues where i had increase max rows in join from 1M to 10M. 2. Now I'm running into the max rows limit again and raising to 100M seems to be too big (my requests are timing out) Here is my query:
Copy code
SET maxRowsInJoin = 10,000,000;
SELECT 
  price.amount,
  price.offerId,
  price.sellerId,
  price.itemId
FROM price
LEFT JOIN offer 
  ON price.sellerId = offer.sellerId
  AND price.offerId = offer.offerId
WHERE 
    offer.internal_item_id = <valid_id>
    OR price.itemId = <valid_id>
I think that pre-filtering might be the way to go. But when I run a query like this I'm running into errors with null values.
Copy code
SELECT price, offerId, sellerId, itemId
FROM
  (SELECT price, offerId, sellerId, itemId
   FROM price
   WHERE itemId = <valid_id>) AS price
LEFT JOIN
  (SELECT sellerId, offerId, internal_item_id
   FROM offer
   WHERE wmart_item_id = <valid_id>) AS offer
ON 
  price.sellerId = offer.sellerId 
  AND price.offerId = offer.offerId;
Does anyone have an idea on what I can do to get my queries to execute when joining two very large tables?
x
for your join case, is it possible to copartition data by sellerId or offerId, that might speed up query for colocated-join
also you just wanna join to see the full results or you want to perform aggregation on the join table?
pre-filter will help to reduce data set to join and pinot query plan should optimize on this already