Re: [HACKERS] star schema and the optimizer

2015-02-28 Thread Marc Cousin
On 27/02/2015 20:01, Marc Cousin wrote: On 27/02/2015 19:45, Tom Lane wrote: I wrote: I had actually thought that we'd fixed this type of problem in recent versions, and that you should be able to get a plan that would look like Nestloop - scan dim1 - Nestloop - scan dim2

Re: [HACKERS] star schema and the optimizer

2015-02-28 Thread Tom Lane
Marc Cousin cousinm...@gmail.com writes: I gave it another look this morning. It works very well with the initial test schema. The situation is much improved for me. I still have one issue: I've extended the test to more than 2 dimensions. I tried your original test script with 3 dimension

Re: [HACKERS] star schema and the optimizer

2015-02-27 Thread Tom Lane
Marc Cousin cousinm...@gmail.com writes: So I gave a look at the optimizer's code to try to understand why I got this problem. If I understand correctly, the optimizer won't do cross joins, except if it has no choice. That's right, and as you say, the planning-speed consequences of doing

Re: [HACKERS] star schema and the optimizer

2015-02-27 Thread Marc Cousin
On 27/02/2015 15:08, Tom Lane wrote: Marc Cousin cousinm...@gmail.com writes: So I gave a look at the optimizer's code to try to understand why I got this problem. If I understand correctly, the optimizer won't do cross joins, except if it has no choice. That's right, and as you say, the

Re: [HACKERS] star schema and the optimizer

2015-02-27 Thread Tom Lane
I wrote: I had actually thought that we'd fixed this type of problem in recent versions, and that you should be able to get a plan that would look like Nestloop - scan dim1 - Nestloop - scan dim2 - indexscan fact table using dim1.a and dim2.b After closer study, I think

Re: [HACKERS] star schema and the optimizer

2015-02-27 Thread Marc Cousin
On 27/02/2015 19:45, Tom Lane wrote: I wrote: I had actually thought that we'd fixed this type of problem in recent versions, and that you should be able to get a plan that would look like Nestloop - scan dim1 - Nestloop - scan dim2 - indexscan fact table using dim1.a

Re: [HACKERS] star schema and the optimizer

2015-02-27 Thread Marc Cousin
On 27/02/2015 15:27, Marc Cousin wrote: On 27/02/2015 15:08, Tom Lane wrote: Marc Cousin cousinm...@gmail.com writes: So I gave a look at the optimizer's code to try to understand why I got this problem. If I understand correctly, the optimizer won't do cross joins, except if it has no

Re: [HACKERS] star schema and the optimizer

2015-02-27 Thread Tom Lane
I wrote: I had actually thought that we'd fixed this type of problem in recent versions, and that you should be able to get a plan that would look like Nestloop - scan dim1 - Nestloop - scan dim2 - indexscan fact table using dim1.a and dim2.b

Re: [HACKERS] star schema and the optimizer

2015-02-27 Thread Tom Lane
Marc Cousin cousinm...@gmail.com writes: On 27/02/2015 15:08, Tom Lane wrote: That's right, and as you say, the planning-speed consequences of doing otherwise would be disastrous. What do you mean by disastrous ? Let's assume you have ten fact tables, so ten join conditions (11 tables

[HACKERS] star schema and the optimizer

2015-02-27 Thread Marc Cousin
Hi all, I've been facing an issue with star schemas for a while. PostgreSQL's performance is not that good without rewriting the query (or at least I couldn't find a way to make it do what I want). Here is a very basic mockup schema I used for the rest of this mail. It's of course too small