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

I read through that thread and summarized the current pending issue as below IIUC.  

a). The most challenging issue is this push down misleads the planner's rows estimation,

which probably be worse than the lack of such push down.  b).  The new generated 

qual may increase the qual execution cost.  c).  Planning time is also increased but

we can not gain much because of that. I just tried to address these issues as below 

based on the patch David has finished a long time ago.   

To address the row estimation issue, The most straightforward way to fix this is to 

ignore the derived clauses when figuring out the RelOptInfo->rows on base relation. 

To note which clause is derived from this patch, I added a new field "EquivalenceClass *

derived" in RestrictInfo. and then added a  included_derived  option in clauselist_selectivity_ext,

during the set_xxx_rel_size function, we can pass the included_derived=false.  This strategy

should be used in get_parameterized_baserel_size.   In all the other cases, include_derived=true

is used. which are finished in commit 2. (Commit 1 is Daivd's patch, I just  rebased it)  

set enable_hashjoin to off;
set enable_mergejoin to off;
set enable_seqscan to on;
regression=# explain analyze select * from tenk1 a, tenk1 b where a.thousand = b.thousand and a.thousand < 100;

                                                                  QUERY PLAN                                                                  

-----------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=27.14..1090.67 rows=10740 width=488) (actual time=0.404..15.006 rows=10000 loops=1)
   ->  Bitmap Heap Scan on tenk1 b  (cost=26.84..385.26 rows=10000 width=244) (actual time=0.350..1.419 rows=1000 loops=1)
         Recheck Cond: (thousand < 100)\
         Heap Blocks: exact=324
         ->  Bitmap Index Scan on tenk1_thous_tenthous  (cost=0.00..24.34 rows=1074 width=0) (actual time=0.238..0.240 rows=1000 loops=1)
               Index Cond: (thousand < 100)
   ->  Memoize  (cost=0.30..0.47 rows=1 width=244) (actual time=0.002..0.006 rows=10 loops=1000)
         Cache Key: b.thousand
         Cache Mode: logical
         Hits: 900  Misses: 100  Evictions: 0  Overflows: 0  Memory Usage: 277kB
         ->  Index Scan using tenk1_thous_tenthous on tenk1 a  (cost=0.29..0.46 rows=1 width=244) (actual time=0.010..0.032 rows=10 loops=100)
               Index Cond: ((thousand = b.thousand) AND (thousand < 100))
 Planning Time: 2.459 ms
 Execution Time: 15.964 ms
(14 rows)

As shown above, with commit 2 the JoinRel's rows estimation is correct now.  but it will mislead

in RelOptInfo which the semantics is used for Path.rows purpose only.  That is what commit 3 does. 

After commit 3, we can see:

regression=# explain analyze select * from tenk1 a, tenk1 b where a.thousand = b.thousand and a.thousand < 100;

                                                                  QUERY PLAN                                                                  

-----------------------------------------------------------------------------------------------------------------------------------------------

 Nested Loop  (cost=24.90..459.16 rows=10740 width=488) (actual time=0.440..16.966 rows=10000 loops=1)
   ->  Bitmap Heap Scan on tenk1 b  (cost=24.61..383.03 rows=1074 width=244) (actual time=0.383..1.546 rows=1000 loops=1)
         Recheck Cond: (thousand < 100)
         Heap Blocks: exact=324
         ->  Bitmap Index Scan on tenk1_thous_tenthous  (cost=0.00..24.34 rows=1074 width=0) (actual time=0.270..0.272 rows=1000 loops=1)
               Index Cond: (thousand < 100)
   ->  Memoize  (cost=0.30..0.47 rows=1 width=244) (actual time=0.002..0.008 rows=10 loops=1000)
         Cache Key: b.thousand
         Cache Mode: logical
         Hits: 900  Misses: 100  Evictions: 0  Overflows: 0  Memory Usage: 277kB
         ->  Index Scan using tenk1_thous_tenthous on tenk1 a  (cost=0.29..0.46 rows=1 width=244) (actual time=0.012..0.050 rows=10 loops=100)
               Index Cond: ((thousand = b.thousand) AND (thousand < 100))
 Planning Time: 2.578 ms
 Execution Time: 17.929 ms
(14 rows)