有效查询包含PostgreSQL中的数组列的条件的表
问题描述:
需要提出一种有效地执行查询的方法,WHERE
子句中的数组和整数列由时间戳列排序。使用PostgreSQL 9.2。有效查询包含PostgreSQL中的数组列的条件的表
我们需要执行的查询是:
SELECT id
from table
where integer = <int_value>
and <text_value> = any (array_col)
order by timestamp
limit 1;
int_value
是一个整数值,并text_value
是1 - 3字母文本值。
表结构是这样的:
Column | Type | Modifiers ---------------+-----------------------------+------------------------ id | text | not null timestamp | timestamp without time zone | array_col | text[] | integer | integer |
我应该如何设计指标/修改查询,以使其尽可能高效?
非常感谢!如果需要更多信息,请告知我,我会尽快更新。
答
PG可以使用阵列上的索引,但是你必须使用数组运算符来代替<text_value> = any (array_col)
使用ARRAY[<text_value>]<@array_col
(https://*.com/a/4059785/2115135)。如果可以查看您创建的是否有效,则可以使用命令SET enable_seqscan=false;
强制pg使用索引。不幸的是GIN
索引不能在整数列上创建,所以你将不得不为这两列创建两个不同的索引。 请参阅此处的执行计划:http://sqlfiddle.com/#!12/66a71/2
好的,谢谢。那么顺序呢?我如何解决这个问题? – 2013-03-19 17:00:40
哦,我错过了。在这种情况下,'index(integer,timestamp)'应该比'(integer)'和'(timestamp)'上的两个单独索引快,但是我恐怕这比我的水平高一点,所以我不能保证你应该尝试一下。 – 2013-03-19 17:21:21