Postgresql 在动态值上创建函数索引

Question

我有这样的查询

select r.timestamp, r,value
   from result_table r
  where timestamp > ( NOW() - INTERVAL '120 hour' )
    and r.id%10=1`

其中id是自动增量主键。

相反，120和10可以通过任何其他数字(由用户根据他的需要决定(。基本上，用户想要具有一些抽取的时间间隔的数据。

显然，它在大量数据上的工作速度太慢。这里的索引应该是什么？

Answer 1

PostgreSQL 支持 SQL 表达式或函数索引

  where
    timestamp > ( NOW() - INTERVAL '120 hour' )
    and r.id % 10 = 1

需要索引(timestamp, (id % 10))才能获得更高的性能。

查询

CREATE INDEX
 timestamp__idmod10
ON 
 result_table
(timestamp, (id % 10))

查看演示

带索引 http://sqlfiddle.com/#!17/8e63b/6
不带索引 http://sqlfiddle.com/#!17/9be99/3

因评论而编辑

谢谢，雷蒙德，但是，(id % 10(并不是那么好，因为而不是 10 可以是任何其他数字。9、11、100、1 等

其他方法使用 generate_series() 和交付的表生成匹配 % number = 1 的 id 列表。
并将该结果集与 IN 子句一起使用。

p.s 此语句假定一个带有 SERIAL 的 id 列和一个等于或小于 100 万条记录的表。另请记住，generate_series()函数需要一些时间。

SQL语句

 SELECT 
   numbers.number FROM (  
      SELECT
        generate_series(1, 1000000) as number
      ) AS numbers 
    WHERE
      numbers.number % number = 1

然后你可以使用索引

CREATE INDEX timestamp_id ON result_table(timestamp, id);

和查询

SELECT 
 *
FROM 
 result_table 
WHERE
   timestamp > ( NOW() - INTERVAL '120 hour' ) 
 AND
   id IN (
     SELECT 
       numbers.number FROM (  
          SELECT
            generate_series(1, 1000000) as number
          ) AS numbers 
        WHERE
          numbers.number % 10 = 1
   )

请参阅带有示例数据的演示 http://sqlfiddle.com/#!17/5013c0/6。