Thread: Partition pruning for Star Schema
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 ? 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. Will this be part of postgreSQL 11 ? Thanks in advance Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Hi, On 2017-12-02 13:56:51 -0700, legrand legrand wrote: > 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). You've posted nearly the same a few days ago: http://archives.postgresql.org/message-id/1511986639005-0.post%40n3.nabble.com Please don't just duplicate threads just because you've not received an answer a few days ago. If you'd done additional research / provided more context *and* linked to the last time you sent something it'd be different. Greetings, Andres Freund
Sorry, I apologize. I though (most) Hackers were not reading General list. Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
On Sun, Dec 3, 2017 at 5:56 AM, legrand legrand <legrand_legrand@hotmail.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
On 04/12/17 16:08, Ashutosh Bapat wrote: > On Sun, Dec 3, 2017 at 5:56 AM, legrand legrand > <legrand_legrand@hotmail.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
On 04/12/17 17:20, Mark Kirkwood wrote: > On 04/12/17 16:08, Ashutosh Bapat wrote: > >> On Sun, Dec 3, 2017 at 5:56 AM, legrand legrand >> <legrand_legrand@hotmail.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. > Actually it won't - sorry. To get that to work, you would need to evaluate the additional subqueries to produce fixed values! The patch for 'runtime partition pruning' might be what you want tho. Cheers Mark
Thank You ! I will monitor this 'runtime partition pruning' patch. This will be better than using Partitioned DIM tables "Partion wise joined" with a multi level partitioned FACT table ;o) Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html