当前位置：首页 > news >正文

postgresql immutable_PostgreSQL 之函数索引

news 来源：原创 2024/5/8 3:48:57

640?wx_fmt=png

PostgreSQL支持函数索引，但函数必须是immutable的，变量不变时，索引本身不会变化的，所以函数当输入参数不变时，结果必须是永恒不变的。

做个实验，一次其中考试的成绩，有一张表存储学生信息，另一张表存储考试成绩，我们通过平均成绩搜索符合成绩的学生，可以使用索引吗？

使用函数索引，可以的。但是前提是，输入学号，得到平均成绩不能变化(当然成绩是不会变化的)，不然数据变化，但索引不会刷新，则搜索的数据与实际不符。

例子：　

create extension btree_gist;

学生信息表

create table t_user (id int8 primary key, name text);

成绩表

create table t_score (id int8, subject text, score int4);

计算平均成绩的函数

create or replace function get_avg_score(int8) returns float8 as $$
  select avg(score)::float8 from t_score where id=$1 group by id;
$$ language sql strict immutable;

插入学生信息数据

    insert into t_user values (1, 'Tim');
    insert into t_user values (2, 'mike');

插入成绩

insert into t_score values ( 1, 'computor', 88);
insert into t_score values ( 1, 'math', 88);
insert into t_score values ( 1, 'english', 88);
insert into t_score values ( 2, 'computor', 56);
insert into t_score values ( 2, 'math', 45);
insert into t_score values ( 2, 'english', 65);

没有索引

test_idx=# explain  select name , get_avg_score(id) from t_user;
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on t_user  (cost=0.00..322.00 rows=1200 width=40)
(1 row)

函数索引

create index idx_t_user_score_1 on t_user using gist (id,  get_avg_score(id));

强制索引扫描

set enable_bitmapscan=off;
set enable_seqscan=off;

test_idx=# explain  select name , get_avg_score(id) from t_user where get_avg_score(id)  < 55;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using idx_t_user_score_1 on t_user  (cost=0.13..8.39 rows=1 width=40)
   Index Cond: (get_avg_score(id) < '55'::double precision)
(2 rows)

test_idx=# select name , get_avg_score(id) from t_user where get_avg_score(id)  < 65;
 name |  get_avg_score
------+------------------
 mike | 55.3333333333333
(1 row)

如果分数登记有误，修正之后

test_idx=# update t_score set score = score + 20 where id =2;
UPDATE 3
test_idx=# select * from t_score;
 id | subject  | score
----+----------+-------
  1 | computor |    88
  1 | math     |    88
  1 | english  |    88
  2 | computor |    76
  2 | math     |    65
  2 | english  |    85
(6 rows)

下面的结果显然是错误的

test_idx=# select ctid, name , get_avg_score(id) from t_user where get_avg_score(id)  < 65;
 ctid  | name |  get_avg_score
-------+------+------------------
 (0,2) | mike | 75.3333333333333
(1 row)

重建索引后，正确返回数据

test_idx=# drop index idx_t_user_score_1;
DROP INDEX
test_idx=# create index idx_t_user_score_1 on t_user using gist (id,  get_avg_score(id));
CREATE INDEX
test_idx=# select ctid, name , get_avg_score(id) from t_user where get_avg_score(id)  < 65;
 ctid | name | get_avg_score
------+------+---------------
(0 rows)

总结，postgresql支持函数索引，但一定要注意函数必须是immutable,当输入不变时结果必须一致。