Re: [HACKERS] Improve OR conditions on joined columns (common star schema problem)

2017-04-03 Thread Tom Lane
Andres Freund writes: > On 2017-02-14 14:18:54 -0500, Tom Lane wrote: >> One point that could use further review is whether the de-duplication >> algorithm is actually correct. I'm only about 95% convinced by the >> argument I wrote in planunionor.c's header comment. > Are

Re: [HACKERS] Improve OR conditions on joined columns (common star schema problem)

2017-04-03 Thread Andres Freund
Hi, On 2017-02-14 14:18:54 -0500, Tom Lane wrote: > I think this might be code-complete, modulo the question of whether we > want an enabling GUC for it. I'm still concerned about the question > of whether it adds more planning time than it's worth for most users. > (Obviously it needs some

Re: [HACKERS] Improve OR conditions on joined columns (common star schema problem)

2017-02-14 Thread Tom Lane
Jim Nasby writes: > On 2/14/17 1:18 PM, Tom Lane wrote: >> One point that could use further review is whether the de-duplication >> algorithm is actually correct. I'm only about 95% convinced by the >> argument I wrote in planunionor.c's header comment. > I'll put some

Re: [HACKERS] Improve OR conditions on joined columns (common star schema problem)

2017-02-14 Thread Jim Nasby
On 2/14/17 1:18 PM, Tom Lane wrote: I think this might be code-complete, modulo the question of whether we want an enabling GUC for it. I'm still concerned about the question of whether it adds more planning time than it's worth for most users. (Obviously it needs some regression test cases

Re: [HACKERS] Improve OR conditions on joined columns (common star schema problem)

2017-02-14 Thread Tom Lane
I wrote: > The main remaining piece of work here is that, as you can see from the > above, it fails to eliminate joins to tables that we don't actually need > in a particular UNION arm. This is because the references to those > tables' ctid columns prevent analyzejoins.c from removing the joins.

Re: [HACKERS] Improve OR conditions on joined columns (common star schema problem)

2017-02-12 Thread Jim Nasby
On 2/12/17 5:06 PM, David Rowley wrote: Yet I've worked with OLTP applications since 2005, and I struggle to recall any many:many joins at all. Interesting... I've run across it numerous times. In any case, for OLTP there's other things you can do fairly easily. Perhaps this optimisation

Re: [HACKERS] Improve OR conditions on joined columns (common star schema problem)

2017-02-12 Thread David Rowley
On 13 February 2017 at 06:32, Tom Lane wrote: > It's not so much poor choices as the cost of the optimization attempt --- > if there's a K-relation OR clause, this will increase the cost of planning > by a factor approaching K+1, whether or not you get a better plan out of >

Re: [HACKERS] Improve OR conditions on joined columns (common star schema problem)

2017-02-12 Thread Tom Lane
David Rowley writes: > This is very interesting. Couldn't this be even more generic and > instead of looking at just the jointree quals, also look at the join > conditions too, as I think you can use this to also transforms queries > such as: The hard part of that

Re: [HACKERS] Improve OR conditions on joined columns (common star schema problem)

2017-02-12 Thread David Rowley
On 12 February 2017 at 13:30, Tom Lane wrote: > I wrote a POC patch for this on a long airplane ride. It's not complete, > and I'm sure there are bugs as well, but it makes your example case > better. What I did about the de-duplication issue is to de-dup using > the CTIDs

Re: [HACKERS] Improve OR conditions on joined columns (common star schema problem)

2017-02-11 Thread Tom Lane
Jim Nasby writes: > On 2/8/17 5:54 PM, Tom Lane wrote: >> Maybe it'd be better to imagine this as something closer to planagg.c, >> that is it knows how to apply a specific high-level optimization that >> might or might not be a win, so it builds a path describing that

Re: [HACKERS] Improve OR conditions on joined columns (common star schema problem)

2017-02-09 Thread Claudio Freire
On Thu, Feb 9, 2017 at 9:50 PM, Jim Nasby wrote: > WHERE t1 IN ('a','b') OR t2 IN ('c','d') > > into > > WHERE f1 IN (1,2) OR f2 IN (3,4) > > (assuming a,b,c,d maps to 1,2,3,4) > > BTW, there's an important caveat here: users generally do NOT want duplicate > rows from

Re: [HACKERS] Improve OR conditions on joined columns (common star schema problem)

2017-02-09 Thread Jim Nasby
On 2/8/17 5:54 PM, Tom Lane wrote: Although ... actually, that may not be the bottleneck for what you're after. The issue here is not so much discovering a clever plan for a union as realizing that the query could be cast as a union in the first place. Right; their current workaround is to