parition pruning issue with postgres - can mondrian save me ? :)
I have this issue with postgres
Let us say fact table A is partitioned over column X - X is a date value stored as integer, and we have monthly partitions based on X's values
Now if I do a query like:
select * from A join B where A.X = B.X and B.Y = 201205 (Assume B is a time dimension and Y is month id)
The query planner goes to look into all the partition tables, and not only on 201205's partition.
Now when I build a mondrian schema, this is a typical scenario where my time dimension and my fact table are joined using surrogate keys and filter can be on any other column in the time dimension. But the query created is not-so optimal for postgres, and the planner hits the entire fact table of the data warehouse. Is there any workaround which we could perform in our schema to solve this problem???
Now IF i make mondrian to do
select * from A join B where A.X = B.X and A.(month_part)=B.Y and B.Y = 201205
and if I add A.(month_part) within the check constraint , it takes the right partition. Can we achieve such a complex join
madhupenta have you solved the problem with partitionated tables?
I have the same problem.
Thanks in advance.