Hello,
Working on Oracle migration POCs, I'm very interested in v11 and declarative
partitioning optimizations.
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 ?
nb: the query has predicates on dimension tables not on columns used for
fact table partitioning:
- country.name vs sales.country_id,
- calendar.year vs sales.calendar_id.
Second question: will some aggregation be pushed to the fact table ?
Something like
select product.name,calendar.month,agg.sum_net
from
(select product_id,calendar_id,sum(net_price) as sum_net
from sales
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') agg
inner join product on (product.id = agg.cust_id)
inner join calendar on (calendar.id = agg.calendar_id)
group by product.name,calendar.month
Thanks in advance (commitfest or patches references are welcome)
Regards
PAscal
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
