398 Star 1.3K Fork 1.5K

GVPopenGauss / openGauss-server

 / 详情

列存表无法使用索引

已完成
任务 成员
创建于  
2020-07-29 16:43

创建列表如下,c1字段创建索引,然后按照c1列进行查询,发现执行计划无法使用索引,造成点查速度慢。

postgres=> \d+ test_1;
Table "sysadmin.test_1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------------------------+-----------+----------+--------------+-------------
c1 | integer | | plain | |
c2 | integer | | plain | |
c3 | character varying(100) | | extended | |
c4 | character(100) | | extended | |
c5 | timestamp(0) without time zone | | plain | |
Indexes:
"idx_1" psort (c1) TABLESPACE pg_default
Has OIDs: no
Options: orientation=column, compression=low

postgres=> explain select c2 from test_1 where c1=10;
QUERY PLAN

Row Adapter (cost=11.31..11.31 rows=2 width=4)
-> CStore Scan on test_1 (cost=0.00..11.31 rows=2 width=4)
Filter: (c1 = 10)
(3 rows)

Time: 0.513 ms

评论 (4)

bin 创建了任务
bin 关联仓库设置为openGauss/openGauss-server
展开全部操作日志

Hey @bin, Welcome to openGauss Community.
All of the projects in openGauss Community are maintained by @opengauss-bot.
That means the developers can comment below every pull request or issue to trigger Bot Commands.
Please follow instructions at https://gitee.com/opengauss/community/blob/master/contributors/command.en.md to find the details.

opengauss-bot 添加了
 
kind/bug
标签

The answer for this question is : when the openGauss kernel judge that the cost of using index is larger than the cost without using index, it would not use index. Analysis is showed as fowllow.
Firstly, creating a Column-Oriented table and an index for it without inserting any data.

postgres=# create table test0907 (
postgres(# i_id integer not null,
postgres(# i_name varchar(24),
postgres(# i_price decimal(5,2),
postgres(# i_data varchar(50),
postgres(# i_im_id integer
postgres(# ) with(ORIENTATION=COLUMN,COMPRESSION=NO);
CREATE TABLE
postgres=#
postgres=# create index index0907 on test0907(i_id);
CREATE INDEX

Check the query plan.

postgres=# explain select i_name from test0907 where i_id = 1000;
QUERY PLAN

Row Adapter (cost=11.22..11.22 rows=2 width=66)
-> CStore Scan on test0907 (cost=0.00..11.22 rows=2 width=66)
Filter: (i_id = 1000)
(3 rows)

The index is not used.
Then inserting a large number of data(0.1 million). And check the query plan again.

postgres=# copy test0907 from '/mnt/data3/zhouxiongjia/opengauss-server/openGauss-server/dest0/single_node/pg_copydir/ds_ship_mode.da
at';
COPY 100000

postgres=# explain select i_name from test0907 where i_id = 1000;
QUERY PLAN

Row Adapter (cost=639.51..639.51 rows=1 width=20)
-> CStore Index Scan using index0907 on test0907 (cost=0.00..639.51 rows=1 width=20)
Index Cond: (i_id = 1000)
(3 rows)

Obviously, the index is used.

蒲甫安 任务状态待办的 修改为已完成
蒲甫安 移除了
 
kind/bug
标签
蒲甫安 添加了
 
question
标签
bin 任务状态已完成 修改为进行中

插入数据后需要做vacuum analyze,否则走不了索引。

bin 任务状态进行中 修改为已完成

登录 后才可以发表评论

状态
负责人
项目
里程碑
Pull Requests
关联的 Pull Requests 被合并后可能会关闭此 issue
分支
开始日期   -   截止日期
-
置顶选项
优先级
预计工期 (小时)
参与者(4)
5622128 opengauss bot 1581905080 5227357 xiangxinyong 1578982846
C++
1
https://gitee.com/opengauss/openGauss-server.git
git@gitee.com:opengauss/openGauss-server.git
opengauss
openGauss-server
openGauss-server

搜索帮助