Accelerator not being used on multi-filter query in partitioned VDS

1

my VDS reflection has partition on “year”, “month”, and “day” column. When i run this query, the accelerator correctly selects and uses the appropriate reflections :

WITH activity_raw AS (
    SELECT 
        instansi_pemilik_api,
        "year",
        "month",
        COUNT(DISTINCT "day") AS active_days,
        COUNT(*) AS total_hits
    FROM "@admin"."splp-logs-joined"
    WHERE
        LOWER(instansi_pemilik_api) != 'tidak terdaftar'
        AND LOWER(apiCreatorTenantDomain) = 'carbon.super'
        AND LOWER(applicationOwner) != 'internal-key-app'
        AND "year" IN ('2025')
        AND "month" IN ('1')
    GROUP BY instansi_pemilik_api, "year", "month"
),
calendar AS (
        SELECT "year", "month", COUNT(DISTINCT "day") total_days
        FROM "minio"."splp-logs"
        GROUP BY "year", "month"
)

SELECT 
    activity_raw.instansi_pemilik_api,
    activity_raw."year",
    activity_raw."month",
    (calendar.total_days - activity_raw.active_days) total_days_no_hits,
    ROUND(CAST(activity_raw.total_hits AS DOUBLE) / CAST(calendar.total_days AS DOUBLE), 3) average_hits_per_day
FROM
    activity_raw
LEFT JOIN
    calendar ON calendar."year" = activity_raw."year" AND
    calendar."month" = activity_raw."month"

It also still uses accelerator when i add a WHERE condition on the activity_raw CTE,like :
”year” IN (‘2025’) or ”month” IN (‘1’)

The problem arises when i add BOTH of the WHERE condition in the CTE like :
”year” IN (‘2025’) AND “month” IN (‘1’)
Then the query suddenly doesn’t use accelerator, causing a significant increase (3x) in query time. Why does that happen ?

2

Additional info,

Accelerator is correctly used on all filter (“year” only, “month” only, “year” + “month”), if i only run the activity_raw CTE, like :

SELECT 
        instansi_pemilik_api,
        "year",
        "month",
        COUNT(DISTINCT "day") AS active_days,
        COUNT(*) AS total_hits
    FROM "@admin"."splp-logs-joined"
    WHERE
        LOWER(instansi_pemilik_api) != 'tidak terdaftar'
        AND LOWER(apiCreatorTenantDomain) = 'carbon.super'
        AND LOWER(applicationOwner) != 'internal-key-app'
        AND "year" IN ('2025')
        AND "month" IN ('1')
    GROUP BY instansi_pemilik_api, "year", "month"
3

@evanbangun Does reflection get matched and not chosen? Can you do the below

ALTER SYSTEM SET planner.verbose_profile = true;

ALTER SYSTEM RESET planner.verbose_profile;

Upload job profile

4

yes it is matched and not chosen

image
image1070×106 18.6 KB

here’s the job profile

f7d1fb9a-0ecc-4034-a45b-d00b4c0dbbd4.zip (37.0 KB)

5

@evanbangun Apologies, my instructions were not super clear, we need the verbose profile to investigate the root cause, so plesse do the below

ALTER SYSTEM SET planner.verbose_profile = true;

Run Below SQL from Job ID# 1737173a-292d-14fb-0338-8f54d0024500

WITH activity_raw AS (
    SELECT 
        instansi_pemilik_api,
        "year",
        "month",
        COUNT(DISTINCT "day") AS active_days,
        COUNT(*) AS total_hits
    FROM "@admin"."splp-logs-joined"
    WHERE
        LOWER(instansi_pemilik_api) != 'tidak terdaftar'
        AND LOWER(apiCreatorTenantDomain) = 'carbon.super'
        AND LOWER(applicationOwner) != 'internal-key-app'
        AND "year" IN ('2025')
        AND "month" IN ('1')
    GROUP BY instansi_pemilik_api, "year", "month"
),
calendar AS (
        SELECT "year", "month", COUNT(DISTINCT "day") total_days
        FROM "minio"."splp-logs"
        GROUP BY "year", "month"
)

SELECT 
    activity_raw.instansi_pemilik_api,
    activity_raw."year",
    activity_raw."month",
    (calendar.total_days - activity_raw.active_days) total_days_no_hits,
    ROUND(CAST(activity_raw.total_hits AS DOUBLE) / CAST(calendar.total_days AS DOUBLE), 3) average_hits_per_day
FROM
    activity_raw
LEFT JOIN
    calendar ON calendar."year" = activity_raw."year" AND
    calendar."month" = activity_raw."month" ;

Reset verbose profile

ALTER SYSTEM RESET planner.verbose_profile;

Thanks,

Bali