Re: Partition pruning for Star Schema

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.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company