Re: Partition pruning for Star Schema

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.

Cheers

Mark