Partition filter not used when in clause

We have a table “posts” partitioned:

    partition by (bucket( 300, ID_PAGE ), month(CREATED_DATE))

when run this query: Dremio use partition filter on ID_PAGE

SELECT * FROM lake.prod.posts WHERE ID_PAGE ='7070561331787219974'

but when run this query: Dremio NO USE partition filter on ID_PAGE

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 (20,3 KB)

4ad63fd5-58c7-475d-8a93-e524f27f18c6.zip (25,8 KB)

Please @Benny_Chow oe @balaji.ramaswamy can you make me a help Please?

@dacopan Thanks for uploading the profiles, let me review and get back to you

1 Like

@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?

yes, give a minutes to upload log

5655036a-3c55-4ceb-bd0d-771adba1488b.zip (41,8 KB)

log4.zip (14,7 KB)

@dacopan Are you able to try and run this on 2 executors? I am still investigating

thank you by your help, let me prepare infraestructure to run on 2 executors

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.

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

1 Like