Partition filter is skipped when table is used in where condition, why?

[pasiasty2077]

Hi,

maybe someone can help me i do want to run very narrow query

SELECT *
FROM my_table
WHERE snapshot_date IN('2023-01-06', '2023-01-07')
 
-- part of the physical plan:
-- Location: PreparedDeltaFileIndex [dbfs:/...]
-- PartitionFilters: [cast(snapshot_date#282634 as string) IN (2023-01-06,2033-01-07)]

it is very fast ~1s, in the execution plan i see it is using those provided datasets as arguments for partition filters.

If I try to provide filter predicate in form of the one column table it does full table scan and it takes 100x longer.

%sql
SELECT *
FROM
  my_table
WHERE snapshot_date IN (
    SELECT snapshot_date
    FROM (VLUES('2023-01-06'), ('2023-01-07')) T(snapshot_date)
  )
 
-- plan
Location: PreparedDeltaFileIndex [dbfs:/...]
ReadSchema: ...

Is there a way to provide hints for parition filters or something to make sure it does not read whole table?

[-werners-]

No hints on partition pruning afaik.

The reason the partitions were not pruned is because the second query generates a completely different plan.

To be able to filter the partitions, a join first has to happen. And in this case it means the table has to be scanned.

[Kaniz]

Hi @Mariusz J​, We haven’t heard from you since the last response from @Werner Stinckens​, and I was checking back to see if his suggestions helped you.

Or else, If you have any solution, please share it with the community, as it can be helpful to others.

Also, Please don't forget to click on the "Select As Best" button whenever the information provided helps resolve your question.