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

**URL:** <https://community.dremio.com/t/accelerator-not-being-used-on-multi-filter-query-in-partitioned-vds/13202>\
**Category:** Uncategorized\
**Created:** [September 15, 2025, 7:18am UTC](https://community.dremio.com/t/accelerator-not-being-used-on-multi-filter-query-in-partitioned-vds/13202 "2025-09-15T07:18:55Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![evanbangun](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/evanbangun/32/6550_2.png) [@evanbangun](https://community.dremio.com/u/evanbangun)\
**Post date:** [September 15, 2025, 7:18am UTC](https://community.dremio.com/t/accelerator-not-being-used-on-multi-filter-query-in-partitioned-vds/13202/1 "2025-09-15T07:18:55Z")

</div>

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 :

```sql
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 ?

---

<div class="post-metadata">

**Author:** ![evanbangun](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/evanbangun/32/6550_2.png) [@evanbangun](https://community.dremio.com/u/evanbangun)\
**Post date:** [September 15, 2025, 7:23am UTC](https://community.dremio.com/t/accelerator-not-being-used-on-multi-filter-query-in-partitioned-vds/13202/2 "2025-09-15T07:23:26Z")

</div>

Additional info,

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

```sql
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"

```

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [September 15, 2025, 5:13pm UTC](https://community.dremio.com/t/accelerator-not-being-used-on-multi-filter-query-in-partitioned-vds/13202/3 "2025-09-15T17:13:05Z")

</div>

@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

---

<div class="post-metadata">

**Author:** ![evanbangun](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/evanbangun/32/6550_2.png) [@evanbangun](https://community.dremio.com/u/evanbangun)\
**Post date:** [September 16, 2025, 4:40am UTC](https://community.dremio.com/t/accelerator-not-being-used-on-multi-filter-query-in-partitioned-vds/13202/4 "2025-09-16T04:40:56Z")

</div>

yes it is matched and not chosen

 ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/8/82d091e99f78f33de0fe03353ec68ef8650b1783.png)

here’s the job profile

[f7d1fb9a-0ecc-4034-a45b-d00b4c0dbbd4.zip](https://community.dremio.com/uploads/short-url/annym92i8TCcqm4Udz2mUjmf87K.zip) (37.0 KB)

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [September 17, 2025, 5:17pm UTC](https://community.dremio.com/t/accelerator-not-being-used-on-multi-filter-query-in-partitioned-vds/13202/5 "2025-09-17T17:17:51Z")

</div>

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

```auto
ALTER SYSTEM SET planner.verbose_profile = true;

```

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

```auto
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

```auto
ALTER SYSTEM RESET planner.verbose_profile;

```

Thanks,

Bali

---

<div class="post-metadata">

**Author:** ![yixesuro](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/yixesuro/32/6769_2.png) [@yixesuro](https://community.dremio.com/u/yixesuro)\
**Post date:** [October 27, 2026, 3:06pm UTC](https://community.dremio.com/t/accelerator-not-being-used-on-multi-filter-query-in-partitioned-vds/13202/6 "2026-10-27T15:06:17Z")

</div>

The two predicates can change the reflection matching decision, but the SQL alone doesn’t show why it’s rejected. Compare the query profile’s acceleration details for the one-filter and two-filter versions, especially the reflection substitution failure reason and the scan plan. Also check that the reflection contains both filter columns with compatible types; the profile should distinguish a matching issue from partition pruning or stale reflection data.