bitmapscan中一个超巨大的cost值的怪现象
本帖最后由 osdba 于 2011-02-14 16:40 编辑
create table t1(id int primary key,name varchar(40));
create table t2(id int primary key,id1 int,name varchar(40));
insert into t1 select generate_series(1,1000000),generate_series(1,1000000);
insert into t2 select generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000);
create index idx_t2_id1 on t2(id1);
osdba=# set enable_bitmapscan=off;
SET
Time: 0.230 ms
osdba=# explain select * from t2 where t2.id1 in (10000,20000);
QUERY PLAN
-------------------------------------------------------
Seq Scan on t2 (cost=0.00..17906.00 rows=2 width=14)
Filter: (id1 = ANY ('{10000,20000}'::integer[]))
(2 rows)
Time: 0.764 ms
osdba=# set enable_seqscan=off;
SET
Time: 0.228 ms
osdba=# explain select * from t2 where t2.id1 in (10000,20000);
QUERY PLAN
-------------------------------------------------------------------------------
Bitmap Heap Scan on t2 (cost=10000000008.63..10000000016.53 rows=2 width=14)
Recheck Cond: (id1 = ANY ('{10000,20000}'::integer[]))
-> Bitmap Index Scan on idx_t2_id1 (cost=0.00..8.62 rows=2 width=0)
Index Cond: (id1 = ANY ('{10000,20000}'::integer[]))
(4 rows)
Time: 0.713 ms
可以看到cost值出来的一个巨大无比的值:cost=10000000008.63..10000000016.53
让人很奇怪,cost值怎么能算出这么大的一个值呢?
后面又细细想了想,操作过程中第 一步关闭了bitmapscan,这样SQL只能走seqscan了,第二步又关闭了seqscan了,这时执行器无法找到一个有效的执行方法,虽然bitmapscan被禁止了,但总得走一个执行方法,最后选择了bitmapscan,为了说明这个bitmapscan是一个禁止的方法,所以给了这个方法一个很大的cost值,这样来表示这个是一个不是用户期望的执行计划。
再后来发现,如果执行器必需走参数设置为被禁止的执行方法,它就会给这个cost一个很大的数字,例如建了一个表,如果这个表上没有索引,而我们又禁止了seqscan,这时执行器只能选seqscan,这时cost值就会设置一个很大的值:
osdba=# create table t3(id int,name varchar(40));
CREATE TABLE
Time: 168.563 ms
osdba=# insert into t3 select generate_series(1,1000000),generate_series(1,1000000);
INSERT 0 1000000
osdba=# set enable_seqscan=off;
SET
Time: 0.228 ms
osdba=# insert into t3 select generate_series(1,1000000),generate_series(1,1000000);
INSERT 0 1000000
Time: 4637.458 ms
osdba=# explain select * from t3 where id in (10000,20000);
QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on t3 (cost=10000000000.00..10000009526.31 rows=3289 width=102)
Filter: (id = ANY ('{10000,20000}'::integer[]))
(2 rows)
create table t1(id int primary key,name varchar(40));
create table t2(id int primary key,id1 int,name varchar(40));
insert into t1 select generate_series(1,1000000),generate_series(1,1000000);
insert into t2 select generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000);
create index idx_t2_id1 on t2(id1);
osdba=# set enable_bitmapscan=off;
SET
Time: 0.230 ms
osdba=# explain select * from t2 where t2.id1 in (10000,20000);
QUERY PLAN
-------------------------------------------------------
Seq Scan on t2 (cost=0.00..17906.00 rows=2 width=14)
Filter: (id1 = ANY ('{10000,20000}'::integer[]))
(2 rows)
Time: 0.764 ms
osdba=# set enable_seqscan=off;
SET
Time: 0.228 ms
osdba=# explain select * from t2 where t2.id1 in (10000,20000);
QUERY PLAN
-------------------------------------------------------------------------------
Bitmap Heap Scan on t2 (cost=10000000008.63..10000000016.53 rows=2 width=14)
Recheck Cond: (id1 = ANY ('{10000,20000}'::integer[]))
-> Bitmap Index Scan on idx_t2_id1 (cost=0.00..8.62 rows=2 width=0)
Index Cond: (id1 = ANY ('{10000,20000}'::integer[]))
(4 rows)
Time: 0.713 ms
可以看到cost值出来的一个巨大无比的值:cost=10000000008.63..10000000016.53
让人很奇怪,cost值怎么能算出这么大的一个值呢?
后面又细细想了想,操作过程中第 一步关闭了bitmapscan,这样SQL只能走seqscan了,第二步又关闭了seqscan了,这时执行器无法找到一个有效的执行方法,虽然bitmapscan被禁止了,但总得走一个执行方法,最后选择了bitmapscan,为了说明这个bitmapscan是一个禁止的方法,所以给了这个方法一个很大的cost值,这样来表示这个是一个不是用户期望的执行计划。
再后来发现,如果执行器必需走参数设置为被禁止的执行方法,它就会给这个cost一个很大的数字,例如建了一个表,如果这个表上没有索引,而我们又禁止了seqscan,这时执行器只能选seqscan,这时cost值就会设置一个很大的值:
osdba=# create table t3(id int,name varchar(40));
CREATE TABLE
Time: 168.563 ms
osdba=# insert into t3 select generate_series(1,1000000),generate_series(1,1000000);
INSERT 0 1000000
osdba=# set enable_seqscan=off;
SET
Time: 0.228 ms
osdba=# insert into t3 select generate_series(1,1000000),generate_series(1,1000000);
INSERT 0 1000000
Time: 4637.458 ms
osdba=# explain select * from t3 where id in (10000,20000);
QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on t3 (cost=10000000000.00..10000009526.31 rows=3289 width=102)
Filter: (id = ANY ('{10000,20000}'::integer[]))
(2 rows)
作者: osdba 发布时间: 2011-02-14
不明白啊。迷迷糊糊。
作者: renxiao2003 发布时间: 2011-02-15