Re: [GENERAL] Equivalence Classes when using IN

2017-10-11 Thread David Rowley
On 12 October 2017 at 10:15, Kim Rose Carlsen wrote: > Why don't I see that predicate (customer_id) pushed into the outer nested > loop so we don't have to sort the whole table on each loop. > > (See original post and follow up for definitions) >

Re: [GENERAL] Equivalence Classes when using IN

2017-10-11 Thread Kim Rose Carlsen
> On 11 Oct 2017, at 21.46, David Rowley wrote: > >> On 12 October 2017 at 08:37, Kim Rose Carlsen wrote: >> >>> Yeah. The ORDER BY creates a partial optimization fence, preventing >>> any such plan from being considered. >> >> I can see in

Re: [GENERAL] Equivalence Classes when using IN

2017-10-11 Thread David Rowley
On 12 October 2017 at 08:37, Kim Rose Carlsen wrote: > >> Yeah. The ORDER BY creates a partial optimization fence, preventing >> any such plan from being considered. >>> > > I can see in the general case it semanticly means different things If you > allow the WHERE to pass

Re: [GENERAL] Equivalence Classes when using IN

2017-10-11 Thread Kim Rose Carlsen
> Yeah. The ORDER BY creates a partial optimization fence, preventing > any such plan from being considered. >> I can see in the general case it semanticly means different things If you allow the WHERE to pass through ORDER BY. A special case can be allowed for WHERE to pass the ORDER BY if

Re: [GENERAL] Equivalence Classes when using IN

2017-10-10 Thread Nico Williams
On Mon, Oct 09, 2017 at 07:44:50PM -0400, Tom Lane wrote: > David Rowley writes: > > If the only reason that is_simple_subquery() rejects subqueries with > > ORDER BY is due to wanting to keep the order by of a view, then > > couldn't we make is_simple_subquery() a

Re: [GENERAL] Equivalence Classes when using IN

2017-10-10 Thread Kim Rose Carlsen
> If the only reason that is_simple_subquery() rejects subqueries with > ORDER BY is due to wanting to keep the order by of a view, then > couldn't we make is_simple_subquery() a bit smarter and have it check > if the subquery is going to be joined to something else, which likely > would destroy

Re: [GENERAL] Equivalence Classes when using IN

2017-10-10 Thread Kim Rose Carlsen
> You would benefit from adding the age column to view_customer, or at > least consider having some view which contains all the columns you'll > ever need from those tables and if you need special views with only a > subset of columns due to some software doing "select * from > viewname;", then

Re: [GENERAL] Equivalence Classes when using IN

2017-10-09 Thread David Rowley
On 10 October 2017 at 12:44, Tom Lane wrote: > David Rowley writes: >> If the only reason that is_simple_subquery() rejects subqueries with >> ORDER BY is due to wanting to keep the order by of a view, then >> couldn't we make

Re: [GENERAL] Equivalence Classes when using IN

2017-10-09 Thread Tom Lane
David Rowley writes: > If the only reason that is_simple_subquery() rejects subqueries with > ORDER BY is due to wanting to keep the order by of a view, then > couldn't we make is_simple_subquery() a bit smarter and have it check > if the subquery is going to be

Re: [GENERAL] Equivalence Classes when using IN

2017-10-09 Thread David Rowley
On 10 October 2017 at 02:51, Tom Lane wrote: > David Rowley writes: >> It's pretty bad practice to have ORDER BY in views. I kinda wish we >> didn't even allow it, but that ship sailed many years ago... > > I think it's actually disallowed by the

Re: [GENERAL] Equivalence Classes when using IN

2017-10-09 Thread Tom Lane
David Rowley writes: > It's not all that clear what your view is doing here. Confusingly > there's a Sort in the plan, yet nothing in the query asked for that, > so I guess that the view must have an ORDER BY. If you get rid of that > the planner would likely use an

Re: [GENERAL] Equivalence Classes when using IN

2017-10-09 Thread David Rowley
On 9 October 2017 at 22:39, Kim Rose Carlsen wrote: > EXPLAIN ANALYZE > SELECT * >FROM customer >JOIN view_customer > ON customer.customer_id = view_customer.customer_id > WHERE age < 20; > >

Re: [GENERAL] Equivalence Classes when using IN

2017-10-09 Thread Kim Rose Carlsen
>On 9 October 2017 at 08:01, Kim Rose Carlsen wrote: >> Is this because postgres never consider IN clause when building equivalence >> class's? > >Only btree equality operators are considered at the moment. After good night sleep and reading the previous discussion, I am no

Re: [GENERAL] Equivalence Classes when using IN

2017-10-08 Thread Tom Lane
David Rowley writes: > There are more details of the discussion in [1], although there's > probably lots more threads to be found if you search the archives. > [1] >

Re: [GENERAL] Equivalence Classes when using IN

2017-10-08 Thread David Rowley
On 9 October 2017 at 08:01, Kim Rose Carlsen wrote: > Is this because postgres never consider IN clause when building equivalence > class's? Only btree equality operators are considered at the moment. > Are there any interests in adding such rule? There's been some discussion on

[GENERAL] Equivalence Classes when using IN

2017-10-08 Thread Kim Rose Carlsen
Hi I have this query where I think it's strange that the join doesn't pull the where condition in since RHS is equal to LHS. It might be easier to expain with an example Setup CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY ); CREATE