Re: [HACKERS] [PATCH] Equivalence Class Filters

On 16 December 2015 at 13:26, Simon Riggs wrote: > There is an interesting real world case where we might get some use of > these thoughts. > > If we have Orders and OrderItems (FK->Orders) > and we also know (and can Assert) Order.order_date <= OrderItems.ship_date > then

Re: [HACKERS] [PATCH] Equivalence Class Filters

On Sun, Dec 20, 2015 at 10:27:35PM +1300, David Rowley wrote: > On 16 December 2015 at 13:26, Simon Riggs wrote: > > > There is an interesting real world case where we might get some > > use of these thoughts. > > > > If we have Orders and OrderItems (FK->Orders) and we

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 7 December 2015 at 16:44, Simon Riggs wrote: > On 6 December 2015 at 16:38, Tom Lane wrote: > > >> Lastly, in most cases knowing that t2.id <= 10 is just not worth all >> >> that much; it's certainly far less useful than an equality condition. >> >>

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 12/16/2015 01:26 AM, Simon Riggs wrote: There is an interesting real world case where we might get some use of these thoughts. If we have Orders and OrderItems (FK->Orders) and we also know (and can Assert) Order.order_date <= OrderItems.ship_date then a restriction on Orders.order_date > X

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 07/12/15 16:44, Simon Riggs wrote: > There are many optimizations we might adopt, yet planning time is a factor. > It seems simple enough to ignore more complex optimizations if we have > already achieved a threshold cost (say 10). Such a test would add nearly > zero time for the common case.

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 12/7/15 7:26 PM, David Rowley wrote: I was talking to Thomas Munro yesterday about this, and he mentioned that it would be quite nice to have some stats on how much time is spent in the planner, vs executor. He came up with the idea of adding a column to pg_stat_statements to record the

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 12/8/15 3:52 AM, Jeremy Harris wrote: On 07/12/15 16:44, Simon Riggs wrote: There are many optimizations we might adopt, yet planning time is a factor. It seems simple enough to ignore more complex optimizations if we have already achieved a threshold cost (say 10). Such a test would add

Re: [HACKERS] [PATCH] Equivalence Class Filters

On Mon, Dec 7, 2015 at 8:26 PM, David Rowley wrote: > The biggest frustration for me is that the way Tom always seems to argue his > point it's as if planning time is roughly the same or more expensive than > execution time, and likely that's true in many cases, but

Re: [HACKERS] [PATCH] Equivalence Class Filters

On Tue, Dec 8, 2015 at 11:12 PM, David Rowley wrote: > My point was more of a response to the general condition that we cannot add > any planner overhead unless the extra CPU cycles spent are almost certainly > going improve the plan. I hope that's an overstatement

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 9 December 2015 at 15:14, Robert Haas wrote: > On Mon, Dec 7, 2015 at 8:26 PM, David Rowley > wrote: > > The biggest frustration for me is that the way Tom always seems to argue > his > > point it's as if planning time is roughly the same

Re: [HACKERS] [PATCH] Equivalence Class Filters

Jim Nasby writes: > On 12/6/15 10:38 AM, Tom Lane wrote: >> I said "in most cases". You can find example cases to support almost any >> weird planner optimization no matter how expensive and single-purpose; >> but that is the wrong way to think about it. What you have

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 12/6/15 10:38 AM, Tom Lane wrote: I said "in most cases". You can find example cases to support almost any weird planner optimization no matter how expensive and single-purpose; but that is the wrong way to think about it. What you have to think about is average cases, and in particular,

Re: [HACKERS] [PATCH] Equivalence Class Filters

On Mon, Dec 7, 2015 at 8:35 AM, Jim Nasby wrote: > On 12/6/15 10:38 AM, Tom Lane wrote: > >> I said "in most cases". You can find example cases to support almost any >> weird planner optimization no matter how expensive and single-purpose; >> but that is the wrong way

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 12/7/15 9:54 AM, Tom Lane wrote: Jim Nasby writes: >On 12/6/15 10:38 AM, Tom Lane wrote: >>I said "in most cases". You can find example cases to support almost any >>weird planner optimization no matter how expensive and single-purpose; >>but that is the wrong

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 12/7/15 10:44 AM, Simon Riggs wrote: There are many optimizations we might adopt, yet planning time is a factor. It seems simple enough to ignore more complex optimizations if we have already achieved a threshold cost (say 10). Such a test would add nearly zero time for the common case. We

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 6 December 2015 at 16:38, Tom Lane wrote: >> Lastly, in most cases knowing that t2.id <= 10 is just not worth all > >> that much; it's certainly far less useful than an equality condition. > > > I'd have thought that my link to a thread of a reported 1100 to 2200 > times >

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 08/12/15 05:27, David G. Johnston wrote: On Mon, Dec 7, 2015 at 8:35 AM, Jim Nasby >wrote: On 12/6/15 10:38 AM, Tom Lane wrote: I said "in most cases". You can find example cases to support almost any

Re: [HACKERS] [PATCH] Equivalence Class Filters

> On 07 Dec 2015, at 22:27, Gavin Flower wrote: > > On 08/12/15 05:27, David G. Johnston wrote: >> On Mon, Dec 7, 2015 at 8:35 AM, Jim Nasby > >wrote: >> >>On 12/6/15 10:38 AM, Tom Lane wrote: >>

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 08/12/15 08:34, Evgeniy Shishkin wrote: On 07 Dec 2015, at 22:27, Gavin Flower wrote: On 08/12/15 05:27, David G. Johnston wrote: On Mon, Dec 7, 2015 at 8:35 AM, Jim Nasby >wrote: On 12/6/15

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 7 December 2015 at 16:55, Jim Nasby wrote: > Unfortunately I've seen a lot of millisecond queries that have 6 figure > estimates, due to data being in cache. So I'm not sure how practical that > would be. > We seem to be agreed that longer running queries may

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 8 December 2015 at 04:35, Jim Nasby wrote: > On 12/6/15 10:38 AM, Tom Lane wrote: > >> I said "in most cases". You can find example cases to support almost any >> weird planner optimization no matter how expensive and single-purpose; >> but that is the wrong way to

Re: [HACKERS] [PATCH] Equivalence Class Filters

David Rowley writes: > On 6 December 2015 at 06:07, Tom Lane wrote: >> Another issue that would need consideration is how to avoid skewing >> planner selectivity estimates with derived clauses that are fully >> redundant with other clauses. >

Re: [HACKERS] [PATCH] Equivalence Class Filters

On 6 December 2015 at 06:07, Tom Lane wrote: > David Rowley writes: > > As of today these Equivalence Classes only incorporate expressions which > > use the equality operator, but what if the above query had been written > as: > > > select *

Re: [HACKERS] [PATCH] Equivalence Class Filters

David Rowley writes: > As of today these Equivalence Classes only incorporate expressions which > use the equality operator, but what if the above query had been written as: > select * from t1 inner join t2 on t1.id = t2.id where t1.id <= 10; > Should we not be

[HACKERS] [PATCH] Equivalence Class Filters

Hi, I'd like to gather up what the community interest might be for this patch. Let me explain: As of today Equivalence Classes are used in the query planner to allow the planner to have more flexibility into the join order by collecting information to describe which expressions are equal to each