创建列表如下,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
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
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.
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。
/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.
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
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.
插入数据后需要做vacuum analyze,否则走不了索引。
登录 后才可以发表评论