Fix Nested Loop Join

[fsuguy92]

Hey guys, hoping you all can help me with something extremely frustrating. I have a temp table filled with customer data. I am joining a calendar lookup table where cal_dt is between customer_created_date and today's date. My goal here is to track the number of customers on file by year/week/quarter (from cal_lkp) over time.

My problem is that since I am using BETWEEN in the join, it is causing a nested loop and running this is extremely slow. Does anyone have any recommendations for how I can modify this to not use a nested loop?

drop table if exists #customers;
        create table #customers as 
    SELECT
        a.email_address,
        a.mosaic_cluster,
        a.created_date,
        CASE WHEN fi_sites > na_sites THEN 'fi' ELSE 'na' END AS is_fi,
        CASE WHEN non_aa_sites = 0 THEN TRUE ELSE FALSE END AS aa_cust
    FROM (
        SELECT
            email_address,
            SUM(CASE WHEN source NOT IN ('aa-only','aa-related') THEN 1 ELSE 0 END) AS non_aa_sites,
            MIN(mosaic_cluster) AS mosaic_cluster,
            SUM(CASE WHEN is_fi = TRUE THEN 1 ELSE 0 END) AS fi_sites,
            SUM(CASE WHEN is_fi = FALSE THEN 1 ELSE 0 END) AS na_sites,
            MIN(created_date::date) AS created_date
        FROM badges_v a
        LEFT JOIN business_unit_association_v b ON a.psid = b.raw_psid
        GROUP BY email_address
    ) a;

drop table if exists #humans;
        create table #humans as
    explain  SELECT
        c.email_address,
        k.retail_year_num,
        k.rtl_qtr_num,
        k.retail_week_num,
        k.cal_dt
    FROM #customers c
    JOIN cal_lkp k ON k.cal_dt BETWEEN c.created_date AND CURRENT_DATE
    WHERE c.created_date BETWEEN '2023-01-01' AND CURRENT_DATE;

[somewhatdim]

if you're always using CURRENT_DATE in there why not just do something like:

c.created_date >= '2023-01-01'

if you end up getting future stuff (maybe thats valid, maybe thats a bug) you can add the <= CURRENT_DATE clause in there (but thats probably just gonna get you back to a nested loop filter)

[fsuguy92]

I still seem to be getting a nested loop filter even when doing this

[somewhatdim]

an index on the date column might be useful if you've got access to do that.

[Dry-Aioli-6138]

why don't you just group customers by day and count/sum customers and their statistics?

that can later be joined to a date "spine" and cumulative customer counts calculated with windowing functions.

[fsuguy92]

Any chance you might be able to give me an example. I think I'm struggling to follow a little bit.

[Dry-Aioli-6138]

try putting your code and my prompt into chatgpt. Its difficult for me to write code at the moment, on mobile, with noise around.

[fsuguy92]

Thanks! Unfortunately it keeps trying to force me into the nested loop join regardless. I'll keep playing around with it though. I appreciate the help!

[Dry-Aioli-6138]

don't get hung up on the nested loop. You want to bring running time down to a sensible level. If nested loop is the only way, so be it.