鐃淑駕申鐃緒申鐃緒申鐃�PostgreSQL鐃所グ鐃緒申奪鐃緒申鐃緒申邯鰹申如鐃緒申鐃緒申戞鐃緒申鐃緒申鐃塾テ￥申鐃瞬ワ申鐃緒申鐃術わ申鐃殉わ申鐃緒申 表鐃緒申鐃緒申鐃緒申鐃緒申鐃熟はバ￥申鐃緒申鐃緒申鐃�8.3鐃叔種申鐃緒申鐃緒申鐃殉わ申鐃緒申鐃緒申 鐃緒申鐃緒申鐃塾（まわ申鐃熟以降の￥申動鐃緒申鐃緒申僂鐃緒申鐃叔緒申鐃緒申鐃緒申鐃緒申鐃殉わ申鐃緒申 鐃殉わ申鐃緒申ANALYZE鐃緒申鐃緒申鐃竣常申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃祝ワ申鐃緒申鐃緒申淵鐃緒申鐃竣ワ申鵐阿鐃峻わ申鐃殉わ申鐃塾で￥申鐃緒申未鐃�ANALYZE鐃薯新わ申鐃緒申鐃峻っわ申鐃緒申鐃渋随申鐃緒申僂鐃緒申泙鐃緒申鐃�
鐃緒申鐃祝器申単鐃緒申鐃巡い鐃緒申錣誌申鐃緒申鐃熟わ申泙鐃緒申腓�申鐃�
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
鐃竣ワ申鐃淑わ申鐃宿のよう鐃緒申tenk1鐃緒申濃鐃駿わ申鐃緒申蠅刻申襪�申砲弔鐃緒申討鐃�項14.2鐃緒申鐃緒申鐃緒申鐃緒申鐃殉わ申鐃緒申鐃緒申
鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃叔器申鐃緒申鐃祝なわ申茲�申坊鐃緒申鐃緒申屬鐃緒申鐃緒申鐃緒申鐃緒申泙鐃緒申鐃�
鐃准￥申鐃緒申鐃緒申鐃緒申鐃緒申唸埒鐃緒申鐃�pg_class鐃緒申鐃初検鐃緒申鐃緒申鐃緒申泙鐃緒申鐃�
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
relpages | reltuples
----------+-----------
358 | 10000鐃緒申鐃緒申鐃緒申鐃粛は最醐申砲鐃緒申離董鐃緒申屮鐃緒申VACUUM鐃殉わ申鐃緒申ANALYZE鐃緒申圓辰鐃緒申鐃緒申鐃緒申里鐃塾でわ申鐃緒申 鐃竣ワ申鐃淑はわ申鐃塾後、鐃銃￥申鐃瞬ワ申鐃緒申亮尊櫃離據鐃緒申鐃緒申鐃緒申鐃緒申鐃出わ申鐃殉わ申鐃淑わ申鐃緒申魯董鐃緒申屮襯刻申鐃緒申鐃緒申鐃峻わ申覆鐃緒申族鐃緒申鐃緒申鐃緒申任鐃緒申法鐃� relpages鐃夙異なわ申鐃順、reltuples鐃熟醐申鐃淳の随申鐃緒申埒鐃緒申鐃獣ｏ申鐃緒申鐃殉わ申鐃緒申鐃順さ鐃緒申泙鐃緒申鐃� 鐃緒申鐃塾常申隋�申鐃緒申鐃峻わ申reltuples鐃緒申同鐃緒申鐃叔わ申鐃塾で￥申鐃粛わ申鐃緒申鐃塾でわ申鐃緒申
鐃緒申鐃緒申WHERE鐃緒申鐃緒申楼肋鐃緒申鐃緒申鐃緒申鐃緒申某覆澆泙鐃緒申腓�申鐃�
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=24.06..394.64 rows=1007 width=244)
Recheck Cond: (unique1 < 1000)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..23.80 rows=1007 width=0)
Index Cond: (unique1 < 1000)
鐃竣ワ申鐃淑わ申WHERE鐃緒申両鐃緒申鮓〆鐃緒申鐃緒申鐃�pg_operator鐃緒申鐃�<鐃初算鐃緒申鐃術わ申鐃緒申鐃緒申鐃駿関随申鐃薯検削申鐃緒申鐃殉わ申鐃緒申
鐃緒申鐃緒申鐃�oprrest鐃緒申鐃緒申飮鐃緒申鐃緒申鐃殉わ申鐃緒申
鐃緒申鐃緒申鐃緒申鐃叔はわ申鐃塾刻申鐃�scalarltsel鐃叔わ申鐃緒申
scalarltsel鐃舜随申鐃熟￥申pg_statistics鐃緒申鐃緒申unique1鐃緒申鐃駿随申分鐃循わ申鐃緒申个鐃緒申泙鐃緒申鐃�
鐃緒申鐃夙わ申鐃巡い鐃緒申錣誌申鐃緒申鐃熟￥申鐃緒申鐃獣縁申鐃緒申pg_stats鐃薯検削申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申単鐃叔わ申鐃緒申
SELECT histogram_bounds FROM pg_stats
WHERE tablename='tenk1' AND attname='unique1';
histogram_bounds
------------------------------------------------------
{0,993,1997,3050,4040,5036,5957,7057,8029,9016,9995}鐃緒申鐃祝￥申"< 1000"鐃緒申鐃緒申鐃緒申鐃緒申鐃駿随申分鐃緒申率鐃緒申鐃緒申个鐃緒申泙鐃緒申鐃� 鐃緒申鐃曙が鐃緒申鐃緒申鐃駿でわ申鐃緒申 鐃緒申鐃緒申鐃駿随申分鐃循は￥申鐃熟囲わ申鐃緒申鐃緒申鐃駿のバワ申鐃獣トわ申分鐃巡し鐃殉わ申鐃緒申 鐃叔わ申鐃塾で￥申鐃峻わ申覆鐃緒申鐃出なわ申覆鐃緒申鐃緒申箸蓮鐃緒申佑鐃緒申鐃緒申鐃出ワ申鐃獣トを見つわ申鐃緒申鐃緒申鐃緒申鐃緒申分鐃夙￥申鐃緒申鐃緒申鐃緒申鐃祝わ申鐃緒申丱鐃緒申奪鐃�鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申襪鰹申箸鐃緒申鐃緒申任鐃緒申鐃� 1000鐃夙わ申鐃緒申鐃粛わ申鐃緒申鐃初か鐃緒申2鐃緒申鐃旬のバワ申鐃獣ト￥申993 - 1997鐃祝にわ申鐃緒申泙鐃緒申鐃� 鐃緒申鐃獣て￥申鐃粛わ申鐃銃バワ申鐃獣トわ申鐃緒申鐃緒申鐃緒申鐃緒申鐃淑�申曚鐃緒申討鐃緒申鐃夙駕申鐃所す鐃緒申函鐃緒申鐃緒申鐃緒申戮鐃淑駕申鐃塾よう鐃祝計誌申鐃緒申鐃暑こ鐃夙わ申鐃叔わ申鐃殉わ申鐃緒申
selectivity = (1 + (1000 - bucket[2].min)/(bucket[2].max - bucket[2].min))/num_buckets
= (1 + (1000 - 993)/(1997 - 993))/10
= 0.100697鐃縦まり、1鐃縦のバワ申鐃獣ワ申鐃緒申鐃塾に￥申2鐃緒申鐃旬のバワ申鐃獣トとわ申鐃緒申鐃緒申鐃緒申率鐃緒申辰鐃緒申鐃緒申鐃塾を、バワ申鐃獣ト随申鐃叔鰹申辰鐃緒申鐃塾となわ申泙鐃緒申鐃� 鐃緒申鐃緒申鐃叔￥申鐃峻の随申鐃緒申鐃粛は￥申鐃緒申鐃緒申鐃駿わ申tenk1鐃緒申濃鐃駿わ申櫃鐃緒申鐃緒せ鐃緒申鐃緒申里箸鐃緒申瞳彁鐃緒申鐃緒申鐃殉わ申鐃緒申
rows = rel_cardinality * selectivity
= 10000 * 0.100697
= 1007 (rounding off)
鐃緒申鐃祝￥申WHERE鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃銃わ申鐃緒申討澆泙鐃緒申腓�申鐃�
EXPLAIN SELECT * FROM tenk1 WHERE stringu1 = 'CRAAAA';
QUERY PLAN
----------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=30 width=244)
Filter: (stringu1 = 'CRAAAA'::name)
鐃緒申鐃緒申鐃瞬わ申鐃殉わ申鐃緒申鐃緒申鐃竣ワ申鐃淑わ申WHERE鐃緒申両鐃緒申鮓〆鐃緒申鐃緒申鐃�=鐃術わ申鐃緒申鐃緒申鐃駿関随申鐃緒申鐃緒申鐃塾常申鐃緒申eqsel鐃薯検削申鐃緒申鐃殉わ申鐃緒申
鐃緒申鐃緒申鐃緒申鐃塾随申鐃緒申任蓮鐃緒申擔鐃淑�申曚鐃緒申鐃緒申立鐃緒申鐃殉わ申鐃緒申
鐃緒申鐃緒申法鐃緒申鐃緒申鐃緒申戮侶鐃緒申鐃祝わ申鐃緒申鐃緒申鐃緒申鐃緒申MCV鐃祝のリス鐃夙わ申鐃緒申鐃術わ申鐃緒申泙鐃緒申鐃�
MCV鐃薯見てみまわ申鐃順う鐃緒申
鐃緒申鐃粛㍼申僂砲覆鐃緒申鵑�鐃緒申鐃緒申弔鐃緒申鐃緒申鐃殉わ申鐃緒申
SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM pg_stats
WHERE tablename='tenk1' AND attname='stringu1';
null_frac | 0
n_distinct | 676
most_common_vals | {EJAAAA,BBAAAA,CRAAAA,FCAAAA,FEAAAA,GSAAAA,JOAAAA,MCAAAA,NAAAAA,WGAAAA}
most_common_freqs | {0.00333333,0.003,0.003,0.003,0.003,0.003,0.003,0.003,0.003,0.003}
CRAAAA鐃緒申MCV鐃塾リス鐃緒申鐃緒申砲鐃緒申鐃殉わ申鐃塾で￥申鐃緒申鐃緒申鐃駿わ申単鐃祝削申鐃緒申鐃駿￥申MCF鐃祝のリス鐃緒申鐃緒申鐃緒申弍鐃緒申鐃緒申鐃緒申鐃旬になわ申泙鐃緒申鐃�
selectivity = mcf[3]
= 0.003鐃緒申鐃緒申同鐃粛￥申鐃緒申鐃所さ鐃緒申鐃峻随申鐃緒申単鐃緒申鐃緒申鐃緒申同鐃粛￥申鐃緒申鐃緒申鐃粛わ申tenk1鐃緒申鐃術でわ申鐃緒申
rows = 10000 * 0.003
= 30
鐃緒申鐃緒申鐃叔￥申同鐃緒申鐃巡い鐃緒申錣誌申鮓�討澆泙鐃緒申鐃� 鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申MCV鐃緒申砲鐃緒申鐃殉わ申鐃緒申
EXPLAIN SELECT * FROM tenk1 WHERE stringu1 = 'xxx';
QUERY PLAN
----------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=15 width=244)
Filter: (stringu1 = 'xxx'::name)鐃粛わ申MCV鐃塾逸申鐃緒申鐃緒申鐃淑わ申鐃緒申隋�申鐃緒申鐃緒申戮鐃宿のよう鐃祝随申鐃所す鐃暑か鐃緒申鐃順き鐃緒申鐃循なわ申泙鐃緒申鐃� 鐃粛わ申鐃緒申鐃緒申鐃祝なわ申鐃緒申鐃祝誌申鐃術わ申鐃緒申鐃緒申鐃祝￥申蓮鐃�MCV鐃緒申鐃駿てわ申鐃緒申鐃駿に関わ申鐃緒申鐃塾種申鐃緒申鐃夙み刻申錣誌申鐃緒申鐃塾でわ申鐃緒申
selectivity = (1 - sum(mvf))/(num_distinct - num_mcv)
= (1 - (0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 +
0.003 + 0.003 + 0.003 + 0.003))/(676 - 10)
= 0.0014559鐃縦まり、MCV鐃緒申鐃緒申鐃駿をすべわ申足鐃緒申鐃緒申錣誌申鐃緒申鐃塾わ申1鐃緒申鐃初差鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃銃￥申鐃緒申鐃緒申他鐃緒申鐃緒申鐃緒申鐃粛の随申鐃叔緒申鐃緒申鐃緒申鐃殉わ申鐃緒申 鐃緒申鐃緒申蓮鐃�MCV鐃淑鰹申鐃緒申鐃緒申粒鐃緒申蓮鐃緒申鐃緒申鐃渋常申慮鐃緒申鐃緒申佑鐃緒申戮討鐃緒申呂辰動鐃緒申佑鐃淑�申曚鐃緒申討鐃緒申襪鰹申箸鐃緒申鐃緒申鐃夙わ申鐃銃わ申鐃暑こ鐃夙になわ申泙鐃緒申鐃� NULL鐃粛わ申存鐃淳わ申鐃淑わ申鐃緒申鐃潤、鐃緒申鐃緒申鐃緒申慮鐃緒申鐃緒申必鐃竣わ申鐃淑わ申鐃緒申鐃夙わ申鐃緒申鐃春わ申鐃銃わ申鐃緒申鐃緒申鐃緒申鐃緒申 鐃淑わ申鐃緒申覆鐃緒申弌鐃淑�申劼鐃緒申鐃銃縁申佑鐃�NULL鐃塾鰹申鐃薯差わ申鐃緒申鐃緒申鐃緒申鐃夙になわ申泙鐃緒申鐃緒申鐃� 鐃緒申鐃緒申埒鐃緒申楼焚鐃緒申里茲�申鐃緒申鐃緒申未坊彁鐃緒申鐃緒申鐃殉わ申鐃緒申
rows = 10000 * 0.0014559
= 15 (rounding off)
鐃緒申鐃述わ申unique1 < 1000鐃緒申鐃緒申僂鐃緒申鐃緒申鐃緒申scalarltsel鐃緒申鐃緒申鐃緒申鐃熟駕申鐃緒申圓鐃緒申鐃緒申砲弔鐃緒申董鐃獣縁申祺緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃塾でわ申鐃緒申鐃緒申
鐃緒申鐃緒申鐃殉でで￥申MCV鐃緒申鐃緒申僂鐃緒申鐃緒申鐃薯見てわ申鐃殉わ申鐃緒申鐃塾で￥申多鐃緒申鐃旬細わ申鐃緒申討鵑垢襪鰹申箸鐃緒申任鐃緒申泙鐃緒申鐃�
unique1鐃熟逸申佞鐃緒申鐃叔わ申鐃暑た鐃潤、MCV鐃緒申存鐃淳わ申鐃殉わ申鐃緒申覆鐃緒申鐃緒申佑鐃渋常申鐃緒申佑鐃銃縁申鐃緒申箸覆襪鰹申箸鐃緒申覆鐃緒申鐃緒申箸鐃緒申鐃緒申里任鐃緒申砲里如鐃緒申鐃熟計誌申鐃緒申鐃塾わ申鐃緒申鐃塾なわ申里任鐃緒申鐃緒申鐃�
鐃緒申佞任呂覆鐃緒申鐃叔は￥申鐃縮常申鐃駿随申分鐃循わ申MCV鐃所ス鐃夙わ申両鐃緒申鐃緒申存鐃淳わ申鐃殉わ申鐃緒申
鐃緒申鐃緒申鐃銃￥申鐃駿随申分鐃循は￥申MCV鐃緒申表鐃緒申鐃緒申鐃緒申鐃緒申貊醐申弔琉鐃緒申屬鐃殉みまわ申鐃緒申
鐃緒申鐃緒申鐃緒申里平鐃緒申鐃緒申圓鐃緒申鐃緒申箸鐃緒申任鐃緒申襪随申瓠�申鐃緒申鐃緒申鐃祝￥申鐃峻わ申鐃殉わ申鐃緒申
鐃緒申鐃塾常申鐃緒申鐃叔は￥申scalarltsel鐃緒申直鐃旬常申鐃緒申鐃純え鐃緒申"< 1000"鐃祝わ申MCV鐃所ス鐃夙の鰹申鐃粛わ申適鐃術わ申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申MCV鐃緒申鐃緒申鐃駿わ申足鐃緒申鐃緒申錣誌申泙鐃緒申鐃�
鐃緒申鐃曙がMCV鐃塾テ￥申鐃瞬ワ申鐃緒申分鐃祝わ申鐃緒申鐃緒申鐃緒申鐃塾な随申鐃緒申鐃緒申鐃緒申鐃駿でわ申鐃緒申
鐃緒申鐃塾醐申鐃駿随申分鐃循わ申鐃緒申鐃銃縁申佑忙箸鐃曙、MCV鐃淑鰹申鐃塾テ￥申鐃瞬ワ申鐃緒申分鐃祝わ申鐃緒申鐃緒申鐃緒申鐃緒申鐃駿わ申鐃緒申蠅件申泙鐃緒申鐃�
鐃緒申鐃緒申鐃銃わ申鐃緒申2鐃縦わ申鐃粛わ申鐃夙み刻申錣誌申董鐃緒申鐃緒申里鐃緒申鐃緒申鐃緒申戮鐃緒申鐃所し鐃殉わ申鐃緒申
鐃純え鐃出￥申鐃淑駕申鐃緒申討鐃緒申鐃殉わ申鐃緒申
EXPLAIN SELECT * FROM tenk1 WHERE stringu1 < 'IAAAAA';
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=3077 width=244)
Filter: (stringu1 < 'IAAAAA'::name)鐃緒申鐃叔わ申stringu1鐃緒申MCV鐃緒申鐃緒申漏鐃叔э申鐃緒申討鐃緒申泙鐃緒申里如鐃緒申鐃緒申鐃緒申任鐃緒申擔鐃淑�申曚鮓�討澆泙鐃緒申鐃�
SELECT histogram_bounds FROM pg_stats
WHERE tablename='tenk1' AND attname='stringu1';
histogram_bounds
--------------------------------------------------------------------------------
{AAAAAA,CQAAAA,FRAAAA,IBAAAA,KRAAAA,NFAAAA,PSAAAA,SGAAAA,VAAAAA,XLAAAA,ZZAAAA}MCV鐃所ス鐃夙を検削申鐃緒申鐃緒申函鐃�stringu1 < 'IAAAAA'鐃緒申鐃緒申鐃緒申頭鐃緒申6鐃緒申鐃旬わ申鐃緒申鐃緒申鐃緒申鐃曙、鐃叔醐申鐃�4鐃緒申鐃旬わ申鐃緒申鐃緒申鐃緒申鐃緒申覆鐃緒申鐃緒申箸鐃緒申錣�申鐃殉わ申鐃緒申 鐃叔わ申鐃塾で￥申鐃曙集鐃縦わ申MCV鐃緒申分鐃祝わ申鐃緒申鐃緒申鐃緒申鐃緒申鐃駿は以駕申鐃塾よう鐃祝なわ申泙鐃緒申鐃�
selectivity = sum(relevant mvfs)
= 0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003
= 0.01833333MCF鐃緒申鐃緒申鐃渋はまわ申鐃緒申MCV鐃緒申表鐃緒申鐃緒申鐃緒申貊醐申弔旅鐃竣鰹申腓�0.03033333鐃叔わ申鐃所、鐃緒申鐃緒申鐃緒申鐃獣わ申鐃駿随申分鐃循わ申表鐃緒申鐃緒申鐃緒申腓�0.96966667鐃叔わ申鐃暑こ鐃夙わ申鐃緒か鐃緒申泙鐃緒申鐃� 鐃淑件申鐃緒申鐃瞬わ申鐃殉わ申鐃緒申鐃緒申NULL鐃緒申存鐃淳わ申鐃殉わ申鐃藷。もし存鐃淳わ申鐃緒申鐃緒申呂鐃緒申鐃緒申能鐃緒申鐃緒申鐃緒申覆鐃緒申鐃出なわ申泙鐃緒申鵝�鐃� IAAAAA鐃夙わ申鐃緒申鐃粛わ申3鐃緒申鐃旬のバワ申鐃獣トの緒申端鐃緒申鐃春になるこ鐃夙わ申鐃叔э申鐃緒申襪鰹申箸鐃緒申任鐃緒申泙鐃緒申鐃� 鐃循なわ申文鐃緒申鐃緒申鐃緒申鐃駿につわ申鐃緒申多鐃緒申鐃渋っぽわ申鐃緒申鐃緒申鐃緒申鐃術わ申鐃緒申函鐃緒申廛鐃緒申覆鐃�IAAAAA鐃緒申蠑�申鐃緒申鐃緒申貊醐申弔鐃緒申擔鐃淑�申曚鐃緒申鐃淑�申凌鐃緒申鐃緒申佑鐃�0.298387鐃祝なわ申泙鐃緒申鐃� 鐃緒申鐃緒申鐃緒申MCV鐃緒申鐃緒申MCV鐃曙集鐃縦につわ申鐃銃の随申鐃緒申鐃粛わ申鐃夙み刻申錣誌申泙鐃緒申鐃�
selectivity = mcv_selectivity + histogram_selectivity * histogram_fraction
= 0.01833333 + 0.298387 * 0.96966667
= 0.307669
rows = 10000 * 0.307669
= 3077 (rounding off)鐃緒申鐃淑�申曚鐃緒申鐃緒申覆鐃淑臣鰹申任鐃緒申里如鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申砲鐃緒申鐃緒申鐃�MCV鐃所ス鐃夙にわ申鐃緒申鐃緒申鐃緒申呂鐃緒申覆蠑�申鐃緒申覆鐃塾でわ申鐃緒申 鐃淑わ申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申佑鐃渋常申鐃緒申鐃緒申鐃重�申任鐃緒申鐃緒申里伴鐃緒申鐃緒申鐃緒申彎鐃緒申鐃熟ほとわ申疋鐃緒申鐃竣ワ申鵐哀鐃緒申蕁種申砲鐃緒申泙鐃緒申鐃緒申鐃� 鐃緒申鐃緒申鐃緒申的鐃淑￥申鐃緒申鐃緒申鐃緒申鐃粛わ申他鐃緒申鐃緒申鐃緒申鐃渋随申鐃緒申兔个鐃緒申鐃緒申鐃叔は￥申鐃緒申鐃緒申鐃粛わ申鐃出わ申鐃緒申鐃緒申鐃緒申鐃駿わ申鐃緒申鐃塾に醐申鐃出わ申鐃緒申泙鐃緒申里如鐃緒申鐃緒申鐃淑ｏ申鐃緒申塀鐃緒申鐃緒申砲鐃緒申鐃緒申鐃駿わ申鐃緒申鐃宿わ申鐃緒申泙鐃緒申鐃�
鐃緒申鐃緒申WHERE鐃緒申鐃淑ｏ申鐃緒申両鐃緒申鐃緒申鐃縦常申鐃緒申討鐃緒申鐃殉わ申鐃順う鐃緒申
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000 AND stringu1 = 'xxx';
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=23.80..396.91 rows=1 width=244)
Recheck Cond: (unique1 < 1000)
Filter: (stringu1 = 'xxx'::name)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..23.80 rows=1007 width=0)
Index Cond: (unique1 < 1000)鐃竣ワ申鐃淑わ申2鐃縦の常申錣�申鐃塾�申鐃緒申討鐃緒申鐃夙駕申鐃所し鐃殉わ申鐃緒申 鐃緒申鐃塾わ申鐃潤、鐃縦￥申鐃塾駈申鐃緒申鐃緒申鐃緒申戮鐃緒申櫃鐃緒申鐃緒さ鐃緒申泙鐃緒申鐃�
selectivity = selectivity(unique1 < 1000) * selectivity(stringu1 = 'xxx')
= 0.100697 * 0.0014559
= 0.0001466
rows = 10000 * 0.0001466
= 1 (rounding off)鐃俊ットマップワ申鐃緒申妊奪鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申砲鐃緒申鐃瞬わ申鐃緒申鐃緒申里反鐃緒申蠅居申鐃緒申埒鐃緒申蓮鐃緒申鐃緒申鐃叔ッワ申鐃緒申鐃叔誌申鐃術わ申鐃緒申鐃緒申鐃塾みわ申反鐃叔わ申鐃暑こ鐃夙わ申鐃緒申鐃春わ申鐃銃わ申鐃緒申鐃緒申鐃緒申鐃緒申 鐃緒申続鐃塾ヒ￥申鐃竣種申鐃出わ申鐃塾ワ申鐃緒申鐃夙随申鐃緒申鳳洞鐃緒申鐃緒申泙鐃緒申里如鐃緒申鐃緒申鐃熟緒申鐃竣でわ申鐃緒申
鐃叔醐申法鐃緒申鐃緒申鐃殉わ申鐃巡い鐃緒申錣誌申鮓�討澆泙鐃緒申腓�申鐃�
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=4.64..456.23 rows=50 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.64..142.17 rows=50 width=244)
Recheck Cond: (unique1 < 50)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.63 rows=50 width=0)
Index Cond: (unique1 < 50)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..6.27 rows=1 width=244)
Index Cond: (t2.unique2 = t1.unique2)tenk1 "unique1 < 50"鐃祝関わ申鐃緒申鐃緒申鐃渋わ申鐃緒申鐃緒申鐃緒申匸鐃緒申襦種申弖鐃緒申鐃緒申鐃緒申鐃宿常申鐃緒申鐃緒申鐃殉わ申鐃緒申 鐃緒申鐃緒申蓮鐃緒申鐃緒申鐃緒申楼呂亡悗鐃緒申鐃緒申鐃緒申同鐃粛に逸申鐃緒申鐃殉わ申鐃緒申 鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃�50鐃緒申unique1鐃駿随申分鐃循の最緒申離丱鐃緒申奪箸砲鐃緒申鐃殉わ申鐃塾で￥申鐃淑駕申鐃塾よう鐃祝なわ申泙鐃緒申鐃�
selectivity = (0 + (50 - bucket[1].min)/(bucket[1].max - bucket[1].min))/num_buckets
= (0 + (50 - 0)/(993 - 0))/10
= 0.005035
rows = 10000 * 0.005035
= 50 (rounding off)
鐃緒申鐃緒申鐃緒申鐃渋わ申t2.unique2 = t1.unique2鐃叔わ申鐃緒申
鐃初算鐃述はよく鐃緒申鐃術わ申鐃緒申単鐃淑わ申=鐃叔わ申鐃緒申鐃緒申鐃緒申鐃緒申鐃駿関随申鐃緒申pg_operator鐃緒申oprjoin鐃藷かわ申鐃緒申鐃所さ鐃曙、eqjoinsel鐃夙なわ申泙鐃緒申鐃�
eqjoinsel鐃緒申tenk2鐃緒申鐃緒申鐃�tenk1鐃緒申両鐃緒申鐃緒申鐃緒申鐃竣常申鐃緒申鮓〆鐃緒申鐃緒申泙鐃緒申鐃�
SELECT tablename, null_frac,n_distinct, most_common_vals FROM pg_stats
WHERE tablename IN ('tenk1', 'tenk2') AND attname='unique2';
tablename | null_frac | n_distinct | most_common_vals
-----------+-----------+------------+------------------
tenk1 | 0 | -1 |
tenk2 | 0 | -1 |鐃緒申鐃緒申両鐃順、鐃緒申鐃駿てわ申鐃粛わ申鐃緒申佞任鐃緒申襪随申瓠�unique2鐃祝関わ申鐃緒申MCV鐃緒申鐃藷がわ申鐃緒申泙鐃緒申鐃� 鐃叔わ申鐃塾で￥申両鐃緒申譟種申鐃緒申鐃緒申慮鐃緒申鐃緒申与鐃緒申鐃�NULL鐃粛わ申鐃緒申分鐃塾みに逸申存鐃緒申鐃緒申鐃緒申鐃暑ゴ鐃所ズ鐃緒申鐃緒申鐃術わ申鐃暑こ鐃夙わ申鐃叔わ申鐃殉わ申鐃緒申
selectivity = (1 - null_frac1) * (1 - null_frac2) * min(1/num_distinct1, 1/num_distinct2)
= (1 - 0) * (1 - 0) / max(10000, 10000)
= 0.0001鐃緒申鐃緒申蓮鐃緒申謄鐃曙ー鐃緒申鐃緒申鐃祝わ申鐃緒申鐃銃￥申1鐃緒申鐃緒申NULL鐃緒申分鐃薯差わ申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃粛随申鐃塾削申鐃緒申鐃粛で鰹申辰鐃緒申佑任鐃緒申鐃� 鐃緒申鐃塾件申腓�申鐃緒申鐃緒申鐃緒申鐃熟わ申鐃塾行随申鐃熟￥申2鐃縦わ申鐃緒申鐃熟のデワ申鐃緒申鐃緒申僂鐃叔誌申戮法鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申戮鐃楯わ申鐃緒申鐃緒申里箸鐃緒申瞳彁鐃緒申鐃緒申鐃殉わ申鐃緒申
rows = (outer_cardinality * inner_cardinality) * selectivity
= (50 * 10000) * 0.0001
= 50
2鐃縦わ申鐃緒申鐃緒申个鐃緒申鐃�MCV鐃所ス鐃夙わ申鐃緒申鐃緒申泙鐃緒申里如鐃�eqjoinsel鐃緒申MCV鐃緒申表鐃緒申鐃緒申鐃緒申鐃緒申貊醐申鐃緒申鐃淑�申侶鐃緒申鐃緒申鐃緒申鐃駿わ申鐃緒申襪随申鐃祝￥申MCV鐃所ス鐃夙わ申直鐃緒申鐃緒申咾鐃緒申泙鐃緒申鐃�
鐃縦わ申鐃緒申貊醐申弔鐃緒申个鐃緒申鐃緒申鐃緒申呂鐃緒申鐃緒申納鐃緒申鐃緒申鐃銃縁申鐃緒申鐃祝￥申暴鐃緒申鐃緒申泙鐃緒申鐃�
inner_cardinality鐃緒申10000鐃緒申鐃縦まわ申tenk2鐃緒申鐃術刻申鐃緒申鐃淑わ申鐃緒申鐃緒申鐃緒申鐃夙種申鐃緒申鐃銃わ申鐃暑こ鐃夙わ申鐃緒申鐃春わ申鐃銃わ申鐃緒申鐃緒申鐃緒申鐃緒申 鐃緒申鐃峻の随申鐃所が50 * 1鐃緒申鐃緒申圓鐃緒申討鐃緒申襦�申弔泙蝓�申鐃渋�申旅埒鐃緒申鐃�tenk2鐃緒申鐃緒申鐃渋�申離鐃緒申鐃叔ッワ申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃峻随申鐃緒申櫃鐃緒申討鐃緒申鐃�EXPLAIN鐃塾緒申鐃熟を検削申鐃緒申鐃暑こ鐃夙わ申分鐃緒申鐃暑か鐃盾し鐃緒申泙鐃緒申鐃� 鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申討呂泙鐃殉わ申鐃緒申 鐃緒申鐃緒申譟種申鐃緒申鐃藷サワ申鐃緒申鐃熟￥申鐃緒申鐃緒申的鐃淑件申鐃竣画が鐃緒申討鐃緒申鐃緒申鐃緒申鐃緒申某鐃緒申蠅居申鐃殉わ申鐃緒申 鐃盾し鐃緒申鐃駿てわ申鐃緒申鐃殉わ申鐃緒申鐃緒申鐃出￥申鐃緒申腑居申鐃緒申鐃緒申鐃緒申鐃所す鐃緒申2鐃縦わ申鐃緒申法鐃緒申同鐃緒申鐃緒申鐃緒申鐃緒申導鐃緒申鐃殉わ申鐃緒申 鐃緒申鐃緒申鐃緒申鐃緒申鐃粛捨醐申鐃緒申鐃緒申鐃緒申鐃淑どわ申鐃竣逸申鐃祝わ申鐃渋随申鐃緒申曚覆鐃緒申腓�申鐃緒申鐃殉わ申鐃緒申
鐃旬細に駈申味鐃緒申鐃緒申辰鐃緒申鐃緒申鐃緒申鐃緒申法鐃緒申董鐃緒申屮鐃淑わ申鐃駿てわ申WHERE鐃緒申鐃緒申鐃緒申砲鐃緒申鐃緒申痢砲離鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申src/backend/optimizer/util/plancat.c鐃叔行わ申鐃殉わ申鐃緒申 鐃緒申鐃緒申鐃緒申鐃緒申戮亡悗鐃緒申鐃緒申鐃緒申的鐃淑ワ申鐃緒申鐃獣ワ申鐃祝つわ申鐃銃わ申src/backend/optimizer/path/clausesel.c鐃祝わ申鐃緒申泙鐃緒申鐃� 鐃初算鐃述醐申有鐃緒申鐃緒申鐃緒申鐃駿関随申鐃祝つわ申鐃銃はわ申鐃緒申鐃銃わ申src/backend/utils/adt/selfuncs.c鐃緒申砲鐃緒申鐃殉わ申鐃緒申