# Partition filter not used when in clause
**URL:** https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879
**Category:** Uncategorized
**Created:** [May 27, 2024, 3:21pm UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879 "2024-05-27T15:21:59Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@dacopan](https://community.dremio.com/u/dacopan)
#### Post date: [May 27, 2024, 3:21pm UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/1 "2024-05-27T15:21:59Z")
We have a table “posts” partitioned:
```sql
partition by (bucket( 300, ID_PAGE ), month(CREATED_DATE))
```
when run this query: Dremio use partition filter on ID\_PAGE
```sql
SELECT * FROM lake.prod.posts WHERE ID_PAGE ='7070561331787219974'
```
but when run this query: Dremio NO USE partition filter on ID\_PAGE
```sql
SELECT * FROM lake.prod.posts WHERE ID_PAGE IN (SELECT ID_PAGE FROM lake.prod.spaces WHERE ID_SPACE = '6654026945b0245faa756c7a')
```
Query profiles:
[71f242df-6a47-4d3a-ac31-7b88ace22efa.zip](https://community.dremio.com/uploads/short-url/3R8iyvSLfq64YqApo9mbq3dxJSI.zip) (20,3 KB)
[4ad63fd5-58c7-475d-8a93-e524f27f18c6.zip](https://community.dremio.com/uploads/short-url/TP60avJ88aQ0Vl3XXvKImU29l.zip) (25,8 KB)
---
### Author:  [@dacopan](https://community.dremio.com/u/dacopan)
#### Post date: [May 28, 2024, 12:33am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/2 "2024-05-28T00:33:49Z")
Please @Benny_Chow oe @balaji.ramaswamy can you make me a help Please?
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [May 28, 2024, 3:35am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/3 "2024-05-28T03:35:30Z")
@dacopan Thanks for uploading the profiles, let me review and get back to you
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [May 28, 2024, 3:44am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/4 "2024-05-28T03:44:01Z")
@dacopan When using a subquery, direct pruning does not happen instead partitions are pruned via runtimefiltering. I do see on table\_functiion 01-xx-06 runtime filter is getting applied but is not effective as zero partitions are getting pruned, see operator metrics under the table function and see column num\_partitions\_pruned. Expand operator details and you can see the filter arrived even before the probe side started scanning. Do you have the executor log file when this query was run?
---
### Author:  [@dacopan](https://community.dremio.com/u/dacopan)
#### Post date: [May 28, 2024, 3:46am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/5 "2024-05-28T03:46:43Z")
yes, give a minutes to upload log
---
### Author:  [@dacopan](https://community.dremio.com/u/dacopan)
#### Post date: [May 28, 2024, 3:52am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/6 "2024-05-28T03:52:47Z")
[5655036a-3c55-4ceb-bd0d-771adba1488b.zip](https://community.dremio.com/uploads/short-url/8l1i4MDVGMqaERv1w5EnzTFchqC.zip) (41,8 KB)
[log4.zip](https://community.dremio.com/uploads/short-url/h2W9abbFCuYclR2ChotVX7rnIiM.zip) (14,7 KB)
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [May 28, 2024, 3:59am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/7 "2024-05-28T03:59:52Z")
@dacopan Are you able to try and run this on 2 executors? I am still investigating
---
### Author:  [@dacopan](https://community.dremio.com/u/dacopan)
#### Post date: [May 28, 2024, 4:02am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/8 "2024-05-28T04:02:20Z")
thank you by your help, let me prepare infraestructure to run on 2 executors
---
### Author:  [@dacopan](https://community.dremio.com/u/dacopan)
#### Post date: [May 30, 2024, 2:25am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/9 "2024-05-30T02:25:31Z")
Hello, Maybe you could validate something additional that would help me improve this? On my side, I have not been able to obtain the necessary infrastructure to port as you suggested.
---
### Author:  [@bogdan.coman](https://community.dremio.com/u/bogdan.coman)
#### Post date: [May 31, 2024, 4:11am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/10 "2024-05-31T04:11:56Z")
This may be related to a known bug (DX-86309/DX-86575), I suggest you retry in the upcoming 25 release, as the fix should be included in it.
Thanks, Bogdan
---
### Author:  [@Ivan\_Chesnov](https://community.dremio.com/u/Ivan_Chesnov)
#### Post date: [June 22, 2024, 7:39am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/11 "2024-06-22T07:39:18Z")
Hey, @dacopan! trying to repro this case, and already have query with a similar plan. But case with NUM\_PARTITIONS\_PRUNED 0 and NUM\_RUNTIME\_FILTERS 1 I can get only in case when SELECT from IN returns to many records, so partitions weren’t pruned. So I need more details:
- are sure that some partitions should be pruned with this condition ( filter “not applied” correctly or it just “not effective”)?
- if you could run this query with debug logs and share it - it would be very helpful!
- did you try it with another condition in IN query?
---
### Author:  [@dacopan](https://community.dremio.com/u/dacopan)
#### Post date: [June 24, 2024, 2:46pm UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/12 "2024-06-24T14:46:59Z")
Hello @Ivan_Chesnov please give a day to make a new test and sent all you are requesting me.
---
### Author:  [@dacopan](https://community.dremio.com/u/dacopan)
#### Post date: [July 19, 2024, 3:16am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/13 "2024-07-19T03:16:19Z")
sorry by delay, @Ivan_Chesnov here you have all test
[Test\_partitions.zip](https://community.dremio.com/uploads/short-url/hc9tdYeOOfVvR2wAVefdtKGT3Iw.zip) (269,6 KB)
---
### Author:  [@Ivan\_Chesnov](https://community.dremio.com/u/Ivan_Chesnov)
#### Post date: [July 19, 2024, 7:12am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/14 "2024-07-19T07:12:44Z")
because of
```auto
partition by (bucket( 300, ID_PAGE ), month(CREATED_DATE))
```
dremio will not prune non-identity partitions - it’s by design.
---
### Author:  [@dacopan](https://community.dremio.com/u/dacopan)
#### Post date: [July 19, 2024, 3:16pm UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/15 "2024-07-19T15:16:43Z")
Hello @Ivan_Chesnov please can you explain more what meaning that why non-identity partitions
---
### Author:  [@dacopan](https://community.dremio.com/u/dacopan)
#### Post date: [July 20, 2024, 12:32am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/16 "2024-07-20T00:32:35Z")
Also why partition pruning works when put direct values in where clause but not works when use subselect
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [July 21, 2024, 9:35pm UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/17 "2024-07-21T21:35:31Z")
@dacopan Partition pruning happens when given direct value, when a sub query is used, partitions will get pruned via runtime filtering
---
### Author:  [@dacopan](https://community.dremio.com/u/dacopan)
#### Post date: [July 21, 2024, 9:40pm UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/18 "2024-07-21T21:40:17Z")
Hi @balaji.ramaswamy yes this behavior already you explained me, but @Ivan_Chesnov said that dremio not pruning with non-identity partitions so now I’m confused how partition pruning works when direct value vs subselect because if not pruning works with subselect, have partition is not usefull
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [July 22, 2024, 4:48am UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/19 "2024-07-22T04:48:21Z")
@dacopan I took a closer look, the filter condition although a partition column, the plan on the query with the direct filter, the filter is applied as part of the DATA scan TABLE\_FUNCTION 01-04. If it is a partition column it would be applied as part of the `manifestContent=[DATA])` `IcebergManifestList` scan `02-03`
Let me see why that part is mixed up?
Do you have the create table statement you used for creating this table?
---
### Author:  [@dacopan](https://community.dremio.com/u/dacopan)
#### Post date: [July 23, 2024, 2:11pm UTC](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879/20 "2024-07-23T14:11:48Z")
Hello @balaji.ramaswamy sure this is the DDL
```sql
CREATE TABLE lake.prod.posts
(
ID varchar,
ID_POST varchar,
ID_PAGE varchar,
ENGAGEMENT double,
CREATED_DATE_STR varchar,
MODIFIED_DATE_STR varchar,
TIMESTAMP_DATE timestamp,
INTERACTIONS integer,
SHARES integer,
SENTIMENT varchar,
GENDER varchar,
IS_ROOT integer,
USERNAME varchar,
NAME varchar,
REGION varchar,
PROVINCE varchar,
CITY varchar,
CONTEXT varchar,
USERNAME_COMMENTS varchar,
COMMENTS integer,
REACTIONS_POST integer,
SORRY_COMPARATIVE integer,
ANGER_COMPARATIVE integer,
HAHA_COMPARATIVE integer,
LOVE_COMPARATIVE integer,
WOW_COMPARATIVE integer,
LIKE_COMPARATIVE integer,
TEXT varchar,
PUBLICATION_TYPE varchar,
PUBLICATION_TYPE_COAUTHOR integer,
VIDEO_VIEWS integer,
CONCEPTS varchar ARRAY,
EMOJIS varchar ARRAY,
VERBS varchar ARRAY,
HASHTAGS varchar ARRAY,
RED integer,
RED_NAME varchar,
TYPE varchar,
IS_RT integer,
MEDIA varchar,
AGE varchar,
EMOTIONS integer,
PERSONALITY integer,
TALK_CATEGORY integer,
IMPRESSIONS_PAID integer,
IMPRESSIONS_ORGANIC integer,
IMPRESSION_TOTAL integer,
REACH_TOTAL integer,
CLICKS_TOTAL integer,
CREATED_DATE timestamp,
MODIFIED_DATE timestamp
)
--@formatter:off
partition by (bucket( 300, ID_PAGE ), TYPE, month(CREATED_DATE));
```
[Next page](https://community.dremio.com/t/partition-filter-not-used-when-in-clause/11879.md?page=2)