Re: BUG #15160: planner overestimates number of rows in join whenthere are more than 200 rows coming from CTE - Mailing list pgsql-hackers
| From | Melanie Plageman |
|---|---|
| Subject | Re: BUG #15160: planner overestimates number of rows in join whenthere are more than 200 rows coming from CTE |
| Date | |
| Msg-id | CAAKRu_bkmaBkjyCM3bKCrHek9+WeMt+XgRengy42SCphnBVhxw@mail.gmail.com Whole thread Raw |
| In response to | Re: BUG #15160: planner overestimates number of rows in join when there are more than 200 rows coming from CTE (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: BUG #15160: planner overestimates number of rows in join when there are more than 200 rows coming from CTE
|
| List | pgsql-hackers |
Given that you have addressed all of my feedback and that it's a pretty
low-risk change, I will change the status to "ready for committer".
There are a couple of minor follow-up clarifications inline that relate mostly
to the questions that I asked in previous emails.
I did have one other question:
Has there been discussion in the past about adding a planner test extension
similar to those in src/test/modules for cardinality estimation? I am imagining
something that is a "soft" check that either the rows estimation that comes out
of calc_joinrel_size_estimate is within an expected range (given differing
estimates across machines) or that the selectivity estimate that comes out of
eqjoinsel is within an expected range. The former seems relatively easy to do
in a manner similar to the test_predtest extension and the latter seems like it
could be done even more trivially.
low-risk change, I will change the status to "ready for committer".
There are a couple of minor follow-up clarifications inline that relate mostly
to the questions that I asked in previous emails.
I did have one other question:
Has there been discussion in the past about adding a planner test extension
similar to those in src/test/modules for cardinality estimation? I am imagining
something that is a "soft" check that either the rows estimation that comes out
of calc_joinrel_size_estimate is within an expected range (given differing
estimates across machines) or that the selectivity estimate that comes out of
eqjoinsel is within an expected range. The former seems relatively easy to do
in a manner similar to the test_predtest extension and the latter seems like it
could be done even more trivially.
On Sat, Nov 17, 2018 at 12:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
(Here, both "outer rel's size" and "inner rel's size" mean the size after
earlier filtering steps.) So that's why we only clamp nd2 and only do so
in eqjoinsel_semi: in the other three cases, we'd be double-counting the
selectivity of earlier filters if we did that.
I just want to make sure I am understanding what the comment is saying: So,
after we calculate the selectivity for inner join, when we return from
calc_joinrel_size_estimate we do this math:
nrows = outer_rows * inner_rows * fkselec * jselec;
and in that equation, the outer and inner rows have been adjusted to account
for any restrictions on the tables, so we don't clamp the ndvs for inner join
in eqjoinsel_inner. However, for semi-join, that calculation is
nrows = outer_rows * fkselec * jselec;
Which means that we have to adjust the rows of the inner side before we get
here?
after we calculate the selectivity for inner join, when we return from
calc_joinrel_size_estimate we do this math:
nrows = outer_rows * inner_rows * fkselec * jselec;
and in that equation, the outer and inner rows have been adjusted to account
for any restrictions on the tables, so we don't clamp the ndvs for inner join
in eqjoinsel_inner. However, for semi-join, that calculation is
nrows = outer_rows * fkselec * jselec;
Which means that we have to adjust the rows of the inner side before we get
here?
So basically the inconsistency here comes from the fact that we define
the meaning of join selectivity differently for inner and semi joins.
I've occasionally wondered if that was a bad choice and we should just
say that selectivity should always be calculated so that the join size
is outer size times inner size times selectivity. But that would
certainly not make for any less need for the selectivity estimator to
do things differently for inner and semi joins, so I am not seeing much
upside to changing it.
I see what you are saying. I got tangled up in this part of the code, so I am
inclined to say that it could stand to be more clear. Selectivity is a ratio,
and, even if you calculate the two sides of the ratio differently, that doesn't
mean the definition of the ratio should be different.
inclined to say that it could stand to be more clear. Selectivity is a ratio,
and, even if you calculate the two sides of the ratio differently, that doesn't
mean the definition of the ratio should be different.
Also, I wanted to address a question you asked in an earlier email:
You wrote:
> Hm. Maybe the "Psemi" and "Pinner" notation is not helpful ... would
> "Ssemi" and "Sinner" be better?
I think Ssemi and Sinner might be more clear--mostly because we haven't used
P/predicate here or in most of the other selectivity estimation comments that I
read. Also, in some cases when we have super limited information and make a
guess, the selectivity feels pretty detached from the join predicate.
> Hm. Maybe the "Psemi" and "Pinner" notation is not helpful ... would
> "Ssemi" and "Sinner" be better?
I think Ssemi and Sinner might be more clear--mostly because we haven't used
P/predicate here or in most of the other selectivity estimation comments that I
read. Also, in some cases when we have super limited information and make a
guess, the selectivity feels pretty detached from the join predicate.
Thanks!
pgsql-hackers by date: