Re: Condition pushdown: why (=) is pushed down into join, but BETWEEN or >= is not?

Hi:

I just tested more cases for the estimation issue for this feature, and we
can
find **we get a more accurate/stable estimation than before**. Here is the
test
cases and result (by comparing the master version and patched version).

create table ec_t110 as select i::int as a from generate_series(1, 110) i;
create table ec_t200 as select i::int as a from generate_series(1, 200) i;
create table ec_t500 as select i::int as a from generate_series(1, 500) i;
create table ec_t800 as select i::int as a from generate_series(1, 800) i;
create table ec_t1000 as select i::int as a from generate_series(1, 1000) i;

analyze;

-- 2 table joins.
explain analyze select * from ec_t1000, ec_t110 where ec_t1000.a =
ec_t110.a and ec_t1000.a > 100; -- (0.9)
explain analyze select * from ec_t1000, ec_t110 where ec_t1000.a =
ec_t110.a and ec_t110.a > 100; -- (0.1)

-- 3 table joins.
explain analyze select * from ec_t1000, ec_t110 , ec_t200 where ec_t1000.a
= ec_t110.a and ec_t110.a = ec_t200.a and ec_t1000.a > 100;
explain analyze select * from ec_t1000, ec_t110 , ec_t200 where ec_t1000.a
= ec_t110.a and ec_t110.a = ec_t200.a and ec_t110.a > 100;
explain analyze select * from ec_t1000, ec_t110 , ec_t200 where ec_t1000.a
= ec_t110.a and ec_t110.a = ec_t200.a and ec_t200.a > 100;

-- 4 table joins.
explain analyze select * from ec_t1000, ec_t110 , ec_t200, ec_t500 where
ec_t1000.a = ec_t110.a and ec_t110.a = ec_t200.a and ec_t500.a = ec_t200.a
and ec_t1000.a > 100;
explain analyze select * from ec_t1000, ec_t110 , ec_t200, ec_t500 where
ec_t1000.a = ec_t110.a and ec_t110.a = ec_t200.a and ec_t500.a = ec_t200.a
and ec_t110.a > 100;
explain analyze select * from ec_t1000, ec_t110 , ec_t200, ec_t500 where
ec_t1000.a = ec_t110.a and ec_t110.a = ec_t200.a and ec_t500.a = ec_t200.a
and ec_t200.a > 100;
explain analyze select * from ec_t1000, ec_t110 , ec_t200, ec_t500 where
ec_t1000.a = ec_t110.a and ec_t110.a = ec_t200.a and ec_t500.a = ec_t200.a
and ec_t500.a > 100;

-- 5 table joins.
explain analyze select * from ec_t1000, ec_t110 , ec_t200, ec_t500, ec_t800
where ec_t1000.a = ec_t110.a and ec_t110.a = ec_t200.a and ec_t500.a =
ec_t200.a and ec_t500.a = ec_t800.a and ec_t1000.a > 100;
explain analyze select * from ec_t1000, ec_t110 , ec_t200, ec_t500, ec_t800
where ec_t1000.a = ec_t110.a and ec_t110.a = ec_t200.a and ec_t500.a =
ec_t200.a and ec_t500.a = ec_t800.a and ec_t110.a > 100;
explain analyze select * from ec_t1000, ec_t110 , ec_t200, ec_t500, ec_t800
where ec_t1000.a = ec_t110.a and ec_t110.a = ec_t200.a and ec_t500.a =
ec_t200.a and ec_t500.a = ec_t800.a and ec_t200.a > 100;
explain analyze select * from ec_t1000, ec_t110 , ec_t200, ec_t500, ec_t800
where ec_t1000.a = ec_t110.a and ec_t110.a = ec_t200.a and ec_t500.a =
ec_t200.a and ec_t500.a = ec_t800.a and ec_t500.a > 100;
explain analyze select * from ec_t1000, ec_t110 , ec_t200, ec_t500, ec_t800
where ec_t1000.a = ec_t110.a and ec_t110.a = ec_t200.a and ec_t500.a =
ec_t200.a and ec_t500.a = ec_t800.a and ec_t800.a > 100;

| Query Id | Real rows | Est. Rows at master | Est. rows with patched |
table # |
|----------+-----------+---------------------+------------------------+---------|
| 1 | 10 | 99 | 10 |
2 |
| 2 | 10 | 10 | 10 |
2 |
| 3 | 10 | 20 | 11 |
3 |
| 4 | 10 | 2 | 11 |
3 |
| 5 | 10 | 11 | 11 |
3 |
| 6 | 10 | 10 | 9 |
4 |
| 7 | 10 | 1 | 9 |
4 |
| 8 | 10 | 6 | 9 |
4 |
| 9 | 10 | 9 | 9 |
4 |
| 10 | 10 | 8 | 8 |
5 |
| 11 | 10 | 1 | 8 |
5 |
| 12 | 10 | 5 | 8 |
5 |
| 13 | 10 | 7 | 8 |
5 |
| 14 | 10 | 8 | 8 |
5 |

In the past, we can just use the qual user provided to do estimation. As for
now, since we introduce the CorrectiveQuals design, we still keep just only
1
qual counted, but we can choose the best one in CorrectiveQuals no matter
which
one is provided by the user. we gain a better and stable estimation because
of this.

I'm happy about the overall design but not pretty confident about the
method to
"choose the best one to keep". So I did some test case as many as I can to
find
something is wrong, so far so good.

I'm also happy with how to keep only one qual in CorrectiveQuals (not
choose the
best one). Assume we just have 1 EC filter in this query for simplicity. At
the
beginning, all the baserel have been impacted by CorrectiveQual. When join 2
relations, we rollback 1 side and keep the other one. when we join this
joinrel
with another rel, we rollback 1 side and keep the other one and so forth.

(rollback is not changing some things which we already computed, it is only
used when estimating size for coming joinrel).

The patchset can be applied cleanly with
9e98583898c347e007958c8a09911be2ea4acfb9.