可以在Postgres的索引中使用稳定函数吗? [英] Is it possible to use a stable function in an index in Postgres?

查看:139
本文介绍了可以在Postgres的索引中使用稳定函数吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我一直在工作一个项目,并且意识到必须在多个查询的WHERE子句中调用一个函数。效果并非完全糟糕,但我希望对其进行改进。因此,我查看了用于索引的文档,其中提到:

I've been working on a project at work and have come to the realization that I must invoke a function in several of the queries' WHERE clauses. The performance isn't terrible exactly, but I would love to improve it. So I looked at the docs for indexes which mentioned that:


索引字段可以是根据表行中一个或多个列的值计算得出的表达式。

An index field can be an expression computed from the values of one or more columns of the table row.

太棒了。所以我尝试创建一个索引:

Awesome. So I tried creating an index:

创建索引idx_foo ON foo_table(stable_function(foo_column));

并收到错误:


错误:索引表达式中的函数必须标记为IMMUTABLE

ERROR: functions in index expression must be marked IMMUTABLE

因此,我读到了函数波动性类别,其中有关于稳定波动性的说法:

So then I read about Function Volatility Categories which had this to say about stable volatility:


,在索引扫描条件下使用包含此类函数的表达式是安全的。

In particular, it is safe to use an expression containing such a function in an index scan condition.

基于短语索引扫描条件我猜这并不意味着实际的索引。那是什么意思呢?是否可以在索引中使用稳定函数?还是我们必须一直努力并确保它可以作为不变函数使用?

Based on the phrasing "index scan condition" I'm guessing it doesn't mean an actual index. So what does it mean? Is it possible to utilize a stable function in an index? Or do we have to go all the way and ensure this would work as an immutable function?

我们正在使用Postgres v9.0.1。

We're using Postgres v9.0.1.

推荐答案

索引扫描条件是 search 条件,可以使用volatile函数,该函数将针对每一行进行调用处理。索引 definition 仅在不可变的情况下才可以使用该函数-也就是说,当使用任何给定的参数集调用该函数时,该函数将始终返回相同的值,并且没有用户可见的副作用。如果稍微考虑一下,您应该能够发现如果函数返回的值与创建索引条目时返回的值不同,那么您会遇到什么麻烦。

An "index scan condition" is a search condition, and can use a volatile function, which will be called for each row processed. An index definition can only use a function if it is immutable -- that is, that function will always return the same value when called with any given set of arguments, and has no user-visible side effects. If you think about it a little, you should be able to see what kind of trouble you could get into if the function might return a different value than what it did when the index entry was created.

您可能会倾向于躺在数据库中并声明一个不可变的函数,但这并不是真的。但是如果您这样做了,数据库可能会做一些您不希望做的令人惊讶的事情。

You might be tempted to lie to the database and declare a function as immutable which isn't really; but if you do, the database will probably do surprising things that you would rather it didn't.

9.0.1中存在可以修复的错误。请升级到9.0。最近的事情

9.0.1 has bugs for which fixes are available. Please upgrade to 9.0.somethingrecent.

http://www.postgresql.org/support/versioning/

这篇关于可以在Postgres的索引中使用稳定函数吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆