--============setup
drop table if exists t_name_1;
create table t_name_1(name text);
create index idx_t_name_1 on t_name_1(name);

drop table if exists t_name_2;
create table t_name_2(name text);

insert into t_name_1 select 
  case when g  = 0   then 'a' 
       when g <=111  then 'b' 
       when g <=222  then 'B'
       else               'A' end
from generate_series(0,29999) g;

insert into t_name_2 select
  case when g  = 0   then 'a' 
       when g <=100  then 'b' 
       when g <=200  then 'B'
       else               'A' end
from generate_series(0,29999) g;

---10000*300
--gdb:breakpoint @ in do_analyze_rel at ../1_patch/src/backend/commands/analyze.c:530
--gdb: set targrows=300000

--alter table t_name_1 ALTER COLUMN name SET STATISTICS 10000;
--alter table t_name_2 ALTER COLUMN name SET STATISTICS 10000;

analyze t_name_1, t_name_2;


select name,count(*) from t_name_1 group by name;
select name,count(*) from t_name_2 group by name;
DROP TABLE
CREATE TABLE
CREATE INDEX
DROP TABLE
CREATE TABLE
INSERT 0 30000
INSERT 0 30000

ANALYZE
 name | count 
------+-------
 A    | 29777
 B    |   111
 b    |   111
 a    |     1
(4 rows)

 name | count 
------+-------
 A    | 29799
 B    |   100
 b    |   100
 a    |     1
(4 rows)

xman1=# 

select tablename, attname, n_distinct, most_common_vals, most_common_freqs,correlation
from pg_catalog.pg_stats where tablename like 't_name_%'\gx

xman1=# select tablename, attname, n_distinct, most_common_vals, most_common_freqs,correlation
from pg_catalog.pg_stats where tablename like 't_name_%'\gx
-[ RECORD 1 ]-----+-----------------------------------
tablename         | t_name_1
attname           | name
n_distinct        | 4
most_common_vals  | {A,B,b}
most_common_freqs | {0.99256665,0.0037,0.0037}
correlation       | 0.9557309
-[ RECORD 2 ]-----+-----------------------------------
tablename         | t_name_2
attname           | name
n_distinct        | 4
most_common_vals  | {A,B,b}
most_common_freqs | {0.9933,0.0033333334,0.0033333334}
correlation       | 0.9600689

xman1=#




--============test in cold env/cold startup
stop-pg/start-pg

set enable_hashjoin=off;
set enable_mergejoin=off;
show enable_hashjoin;
show enable_mergejoin;


explain (ANALYZE,VERBOSE,COSTS,SETTINGS,BUFFERS,TIMING,MEMORY,IO)
select count(*)
from t_name_2 n2, t_name_1 n1
where n1.name = 'B'
  and n2.name = 'B' collate "case_insensitive";