Thread: Partition pruning / agg push down for star schema in pg v11

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

Adding partitioning on the dim tables, with the same keys as those used in
the fact table,
gives any star schema a good chance to use Partition Wise Join / Aggregate
plans.

Will test it soon
Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Partition pruning doen't work in this case
as described at
http://www.postgresql-archive.org/Partition-pruning-for-Star-Schema-td5995163.html#a5995168

Partition wise join works for ONLY ONE dim table (that is better than
nothing).



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html