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 salesinner join product on (product.id = sales.cust_id)inner join country on (country.id =
sales.country_id)innerjoin 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') agginner 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