Re: [HACKERS] <> join selectivity estimate question

On Thu, Sep 14, 2017 at 4:30 AM, Tom Lane wrote: > Thomas Munro writes: >> On Wed, Sep 6, 2017 at 11:14 PM, Ashutosh Bapat >> wrote: >>> I added some "stable" tests to your patch taking inspiration from the >>>

Re: [HACKERS] <> join selectivity estimate question

On Thu, Sep 14, 2017 at 4:19 AM, Thomas Munro wrote: > On Wed, Sep 6, 2017 at 11:14 PM, Ashutosh Bapat > wrote: >> On Fri, Jul 21, 2017 at 4:10 AM, Thomas Munro >> wrote: >>> That just leaves the

Re: [HACKERS] <> join selectivity estimate question

Thomas Munro writes: > On Wed, Sep 6, 2017 at 11:14 PM, Ashutosh Bapat > wrote: >> I added some "stable" tests to your patch taking inspiration from the >> test SQL file. I think those will be stable across machines and runs. >>

Re: [HACKERS] <> join selectivity estimate question

On Wed, Sep 6, 2017 at 11:14 PM, Ashutosh Bapat wrote: > On Fri, Jul 21, 2017 at 4:10 AM, Thomas Munro > wrote: >> That just leaves the question of whether we should try to handle the >> empty RHS and single-value RHS cases using

Re: [HACKERS] <> join selectivity estimate question

Simon Riggs writes: > Why isn't this an open item for PG10? Why should it be? This behavior has existed for a long time. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] <> join selectivity estimate question

On 6 September 2017 at 04:14, Ashutosh Bapat wrote: > On Fri, Jul 21, 2017 at 4:10 AM, Thomas Munro > wrote: >> >> Thanks. Bearing all that in mind, I ran through a series of test >> scenarios and discovered that my handling for

Re: [HACKERS] <> join selectivity estimate question

On Fri, Jul 21, 2017 at 4:10 AM, Thomas Munro wrote: > > Thanks. Bearing all that in mind, I ran through a series of test > scenarios and discovered that my handling for JOIN_ANTI was wrong: I > thought that I had to deal with inverting the result, but I now see >

Re: [HACKERS] <> join selectivity estimate question

On Fri, Jul 21, 2017 at 8:21 AM, Tom Lane wrote: > Ashutosh Bapat writes: >> On Thu, Jul 20, 2017 at 5:30 PM, Thomas Munro >> wrote: >>> Does anyone know how to test a situation where the join is reversed >>>

Re: [HACKERS] <> join selectivity estimate question

Ashutosh Bapat writes: > On Thu, Jul 20, 2017 at 5:30 PM, Thomas Munro > wrote: >> Does anyone know how to test a situation where the join is reversed >> according to >> get_join_variables, or "complicated cases where we can't tell

Re: [HACKERS] <> join selectivity estimate question

On Thu, Jul 20, 2017 at 5:30 PM, Thomas Munro wrote: > On Thu, Jul 20, 2017 at 11:47 PM, Ashutosh Bapat > wrote: >> On Thu, Jul 20, 2017 at 11:04 AM, Thomas Munro >> wrote: >>> On Fri, Jun 2, 2017 at

Re: [HACKERS] <> join selectivity estimate question

On Thu, Jul 20, 2017 at 11:47 PM, Ashutosh Bapat wrote: > On Thu, Jul 20, 2017 at 11:04 AM, Thomas Munro > wrote: >> On Fri, Jun 2, 2017 at 4:16 AM, Tom Lane wrote: >>> I don't think it does really. The thing

Re: [HACKERS] <> join selectivity estimate question

On Thu, Jul 20, 2017 at 11:04 AM, Thomas Munro wrote: > On Fri, Jun 2, 2017 at 4:16 AM, Tom Lane wrote: >> I don't think it does really. The thing about a <> semijoin is that it >> will succeed unless *every* join key value from the inner query

Re: [HACKERS] <> join selectivity estimate question

On Fri, Jun 2, 2017 at 4:16 AM, Tom Lane wrote: > I don't think it does really. The thing about a <> semijoin is that it > will succeed unless *every* join key value from the inner query is equal > to the outer key value (or is null). That's something we should consider > to

Re: [HACKERS] <> join selectivity estimate question

Dilip Kumar writes: > Actually, I was not proposing this patch instead I wanted to discuss > the approach. I was claiming that for > non-equal JOIN_SEMI selectivity estimation instead of calculating > selectivity in an existing way i.e > = 1- (selectivity of equal

Re: [HACKERS] <> join selectivity estimate question

On Thu, Jun 1, 2017 at 8:24 PM, Robert Haas wrote: > On Wed, May 31, 2017 at 1:18 PM, Dilip Kumar wrote: >> + if (jointype = JOIN_SEMI) >> + { >> + sjinfo->jointype = JOIN_INNER; >> + } > > That is pretty obviously

Re: [HACKERS] <> join selectivity estimate question

On Wed, May 31, 2017 at 1:18 PM, Dilip Kumar wrote: > + if (jointype = JOIN_SEMI) > + { > + sjinfo->jointype = JOIN_INNER; > + } That is pretty obviously half-baked and completely untested. -- Robert Haas EnterpriseDB:

Re: [HACKERS] <> join selectivity estimate question

On Fri, Mar 17, 2017 at 6:49 PM, Thomas Munro wrote: > Right. If I temporarily hack neqjoinsel() thus: > > result = 1.0 - result; > + > + if (jointype == JOIN_SEMI) > + result = 1.0; > + > PG_RETURN_FLOAT8(result); > } I was

Re: [HACKERS] <> join selectivity estimate question

On Sat, Mar 18, 2017 at 11:49 AM, Thomas Munro wrote: > On Sat, Mar 18, 2017 at 6:14 AM, Tom Lane wrote: >> After a bit more thought, it seems like the bug here is that "the >> fraction of the LHS that has a non-matching row" is not one minus >>

Re: [HACKERS] <> join selectivity estimate question

On Sat, Mar 18, 2017 at 6:14 AM, Tom Lane wrote: > After a bit more thought, it seems like the bug here is that "the > fraction of the LHS that has a non-matching row" is not one minus > "the fraction of the LHS that has a matching row". In fact, in > this example, *all* LHS

Re: [HACKERS] <> join selectivity estimate question

Robert Haas writes: > On Fri, Mar 17, 2017 at 1:14 PM, Tom Lane wrote: >> It would not be too hard to convince me that neqjoinsel should >> simply return 1.0 for any semijoin/antijoin case, perhaps with >> some kind of discount for nullfrac. Whether or

Re: [HACKERS] <> join selectivity estimate question

On Fri, Mar 17, 2017 at 1:14 PM, Tom Lane wrote: > After a bit more thought, it seems like the bug here is that "the > fraction of the LHS that has a non-matching row" is not one minus > "the fraction of the LHS that has a matching row". In fact, in > this example, *all* LHS

Re: [HACKERS] <> join selectivity estimate question

I wrote: > The problem here appears to be that we don't have any MCV list for > the "twothousand" column (because it has a perfectly flat distribution), > and the heuristic that eqjoinsel_semi is using for the no-MCVs case > is falling down badly. Oh ... wait. eqjoinsel_semi's charter is to

Re: [HACKERS] <> join selectivity estimate question

Robert Haas writes: > The relevant code is in neqsel(). It estimates the fraction of rows > that will be equal, and then does 1 - that number. Evidently, the > query planner thinks that l1.l_suppkey = l2.l_suppkey would almost > always be true, and therefore l1.l_suppkey

Re: [HACKERS] <> join selectivity estimate question

On Fri, Mar 17, 2017 at 1:54 AM, Thomas Munro wrote: > SELECT * >FROM lineitem l1 > WHERE EXISTS (SELECT * > FROM lineitem l2 > WHERE l1.l_orderkey = l2.l_orderkey); > > -> estimates 59986012 rows, actual rows 59,986,052

[HACKERS] <> join selectivity estimate question

Hi hackers, While studying a regression reported[1] against my parallel hash join patch, I noticed that we can also reach a good and a bad plan in unpatched master. One of the causes seems to be the estimated selectivity of a semi-join with an extra <> filter qual. Here are some times I