Join happening when it's not needed

join

(Camila Fracaro) #1

I noticed that my Explore is creating SQL using join when it’s not needed, is there a way to disable this? It’s time costly to have a join when it’s not needed.

This is a real example, I just changed the table and variable names. As you can see, I’m only using tablea, but it’s doing a join on tableb, c and d.
SELECT
COALESCE(SUM(a.variable1), 0) AS a_variable1
FROM tablea AS a
LEFT JOIN tableb AS b ON a.tablea_id = b.tableb_id
LEFT JOIN tablec AS c ON b.tableb_id = c.tablec_id
LEFT JOIN tabled AS d ON (CAST(d.tabled_id AS INT64)) = a.tablea_id2

WHERE ((((_PARTITIONTIME ) >= ((TIMESTAMP(FORMAT_TIMESTAMP(’%F %T’, TIMESTAMP_ADD(TIMESTAMP_TRUNC(TIMESTAMP(FORMAT_TIMESTAMP(’%F %T’, CURRENT_TIMESTAMP(), ‘Timezone’)), DAY), INTERVAL -1 DAY)), ‘Timezone’))) AND (_PARTITIONTIME ) < ((TIMESTAMP(FORMAT_TIMESTAMP(’%F %T’, TIMESTAMP_ADD(TIMESTAMP_ADD(TIMESTAMP_TRUNC(TIMESTAMP(FORMAT_TIMESTAMP(’%F %T’, CURRENT_TIMESTAMP(), ‘Timezone’)), DAY), INTERVAL -1 DAY), INTERVAL 1 DAY)), ‘Timezone’))))))
AND (-- can_see_all
1=1
)
LIMIT 500


(romain.ducarrouge) #2

Hey @Camila_Fracaro
Thanks for this information.

There are multiple reasons why a join would be added in the generated SQL:

  • the use of the required_joins parameter in the Explore.
  • possibly some fields being used as filters on the query, but not having any values fed in (in this case it seems we use a templated filter maybe as there is the filter AND (-1=1) )
  • the way the explore if defined as well

Would you be able to reach out to support@looker.com with the details of this issue so that we can look into this further for you?
Thanks a lot!


(Camila Fracaro) #3

Thanks for your response Romain. We were actually using the other tables for the part (1 = 1) with liquid templating. We are filtering using user attributes, will look into how to do it without forcing those joins.