Re: Partition pruning for Star Schema

On 04/12/17 17:20, Mark Kirkwood wrote:

> On 04/12/17 16:08, Ashutosh Bapat wrote:
>
>> On Sun, Dec 3, 2017 at 5:56 AM, legrand legrand
>> <legrand_legrand(at)hotmail(dot)com> wrote:
>>> Hello,
>>>
>>> I have a typical star schema, having dimension tables "product",
>>> "calendar"
>>> and "country" and a fact table "sales".
>>> This fact table is partitionned by time (range by month) and country
>>> (list).
>>>
>>> Will query like:
>>>
>>> select product.name, calendar.month, sum(sales.net_price)
>>> from sales
>>>   inner join product on (product.id = sales.cust_id)
>>>   inner join country on (country.id = sales.country_id)
>>>   inner join calendar on (calendar.id = sales.calendar_id)
>>> where
>>>   country.name = 'HERE'
>>>   and calendar.year = '2017'
>>> group by product.name,calendar.month
>>>
>>> be able to identify needed partitions ?
>>>
>> AFAIU partition pruning, it works only with the partition key columns.
>> So, if country.name and calendar.year are the partition keys partition
>> pruning would identify the needed partitions from those tables. But
>> planner doesn't know that calendar.year is somehow related to
>> calendar.id and then transfer that knowledge so that partitions of
>> sales can be identified.
>>
>
> If you can get your code to perform a star transformation on this type
> of query, then you might see some partition pruning.
>

Actually it won't - sorry. To get that to work, you would need to
evaluate the additional subqueries to produce fixed values! The patch
for 'runtime partition pruning' might be what you want tho.

Cheers

Mark