Re: [HACKERS] EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

On Fri, Jul 1, 2016 at 11:45 AM, Alvaro Herrera wrote: > Merlin Moncure wrote: > >> It's pretty easy to craft a query where you're on the winning side, >> but what's the worst case of doing two pass...is constant folding a >> non trivial fraction of planning time? > >

Re: [HACKERS] EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

Merlin Moncure wrote: > It's pretty easy to craft a query where you're on the winning side, > but what's the worst case of doing two pass...is constant folding a > non trivial fraction of planning time? One thing that has been suggested is to re-examine the plan after planning is done, and if

Re: [HACKERS] EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

On Fri, Jul 1, 2016 at 12:00 PM, Merlin Moncure wrote: > Sure (I didn't put you on that position, just thinking out loud). The > problem with UNION ALL is that it's only safe to do so when you know > for sure the both sides of the partition are non-overlapping. The > author

Re: [HACKERS] EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Robert Haas writes: > > On Tue, Jun 21, 2016 at 4:18 PM, Merlin Moncure wrote: > >> explain analyze select * from foo where false or exists (select 1 from > >> bar where good and foo.id = bar.id); --

Re: [HACKERS] EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

On Fri, Jul 1, 2016 at 10:27 AM, Robert Haas wrote: > On Fri, Jul 1, 2016 at 10:20 AM, Merlin Moncure wrote: >> Yeah. Also, even if you could parse out those cases, it's major >> optimization fence. Consider if you have an ORDER BY clause here: >> >>

Re: [HACKERS] EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

On Fri, Jul 1, 2016 at 10:11 AM, Tom Lane wrote: > Robert Haas writes: >> On Fri, Jul 1, 2016 at 9:52 AM, Tom Lane wrote: >>> Maybe, but neither UNION nor UNION ALL would duplicate the semantics >>> of OR, so there's some handwaving

Re: [HACKERS] EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

On Fri, Jul 1, 2016 at 10:20 AM, Merlin Moncure wrote: > Yeah. Also, even if you could parse out those cases, it's major > optimization fence. Consider if you have an ORDER BY clause here: > > SELECT FROM foo WHERE a OR b ORDER BY c; > > ... by pushing inside a union, you're

Re: [HACKERS] EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

On Fri, Jul 1, 2016 at 9:11 AM, Tom Lane wrote: > Robert Haas writes: >> On Fri, Jul 1, 2016 at 9:52 AM, Tom Lane wrote: >>> Maybe, but neither UNION nor UNION ALL would duplicate the semantics >>> of OR, so there's some handwaving

Re: [HACKERS] EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

Robert Haas writes: > On Fri, Jul 1, 2016 at 9:52 AM, Tom Lane wrote: >> Maybe, but neither UNION nor UNION ALL would duplicate the semantics >> of OR, so there's some handwaving here that I missed. > SELECT * FROM foo WHERE a = 5 OR a = 4 > isn't

Re: [HACKERS] EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

On Fri, Jul 1, 2016 at 9:52 AM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Jun 21, 2016 at 4:18 PM, Merlin Moncure wrote: >>> explain analyze select * from foo where false or exists (select 1 from >>> bar where good and foo.id

Re: [HACKERS] EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

Robert Haas writes: > On Tue, Jun 21, 2016 at 4:18 PM, Merlin Moncure wrote: >> explain analyze select * from foo where false or exists (select 1 from >> bar where good and foo.id = bar.id); -- A >> explain analyze select * from foo where exists

Re: [HACKERS] EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

On Tue, Jun 21, 2016 at 4:18 PM, Merlin Moncure wrote: > Observe the following test case (apologies if this is a well > understood problem): > > create temp table foo as select generate_series(1,100) id; > create index on foo(id); > > create temp table bar as select id, id