sql - 在一个时间段内的相等间隔内对 Mins 和 Maxs 进行慢速 PostgreSQL 查询

Question

我的系统有很多进行测量的设备。这些测量值存储在表“sample_data”中。每台设备一年内可能有 1000 万次测量。大多数情况下，用户只对某个时间段内相等间隔内的 100 分钟最大对感兴趣，例如在过去 24 小时或过去 53 周内。为了获得这 100 分钟和最大值，将周期分为 100 个相等的间隔。从每个间隔中提取最小值和最大值。您会推荐最有效的数据查询方法吗？到目前为止，我已经尝试了以下查询:

WITH periods AS (
  SELECT time.start AS st, time.start + (interval '1 year' / 100) AS en
  FROM generate_series(now() - interval '1 year', now(), interval '1 year' / 100) AS time(start)
)
SELECT s.* FROM sample_data s
  JOIN periods ON s.time BETWEEN periods.st AND periods.en 
  JOIN devices d ON d.customer_id = 23
  WHERE
    s.id = (SELECT id FROM sample_data WHERE device_id = d.id and time BETWEEN periods.st AND periods.en ORDER BY sample ASC LIMIT 1) OR
    s.id = (SELECT id FROM sample_data WHERE device_id = d.id and time BETWEEN periods.st     AND periods.en ORDER BY sample DESC LIMIT 1)

此查询大约用了 4 秒。它不是很合适，因为 sample_data 表每个设备最多可以包含 1000 万行。我看到它不是以非常优化的方式运行，但不知道为什么。我以为我已经为这个查询中使用的所有关键字段建立了索引。

你能给我推荐一种更快地获取此类统计信息的方法吗？

表“设备”:

       Column       |            Type             |                      Modifiers                       
--------------------+-----------------------------+------------------------------------------------------
 id                 | integer                     | not null default nextval('devices_id_seq'::regclass)
 customer_id        | integer                     | 

    <Other fields skipped as they are not involved into the query>
Indexes:
"devices_pkey" PRIMARY KEY, btree (id)
"index_devices_on_iccid" UNIQUE, btree (iccid)

它有 12 台设备，而查询中指定的 customer_id = 23 只有 4 台设备。

表“sample_data”:

     Column     |            Type             |                        Modifiers                         
----------------+-----------------------------+----------------------------------------------------------
id             | integer                     | not null default nextval('sample_data_id_seq'::regclass)
sample         | numeric                     | not null
time           | timestamp without time zone | not null
device_id      | integer                     | not null
customer_id    | integer                     | not null
Indexes:
"sample_data_pkey" PRIMARY KEY, btree (id)
"sample_data_device_id_time_sample_idx" btree (device_id, "time", sample)

它有大约 170 万行。属于 customer_id = 23 的 4 个设备的每个设备大约 720K 行。该表现在由测试数据填充。

“选择版本()”结果:

PostgreSQL 9.3.5 on x86_64-apple-darwin13.3.0, compiled by Apple LLVM version 5.0 (clang-500.2.79) (based on LLVM 3.3svn), 64-bit

track_io_timing 设置为“开”

EXPLAIN (ANALYZE, BUFFERS) 结果在这里: http://explain.depesz.com/s/kA12

Answer 1

我的猜测是性能的驱动因素是 where 子句中的查询。让我们看看其中的一个:

WHERE s.id = (SELECT sd.id
              FROM sample_data sd
              WHERE sd.device_id = d.id and
                    sd.time BETWEEN periods.st AND periods.en
              ORDER BY sd.sample ASC
              LIMIT 1
             ) 

您在 sample_data(devide_id, time, sample) 上有一个索引，并且您希望数据库引擎使用此索引。不幸的是，它只能为 where 子句充分利用索引。由于 between，它可能不会使用 order by 的索引。

是否可以使用time来编写order by？

WHERE s.id = (SELECT id
              FROM sample_data
              WHERE device_id = d.id and
                    time BETWEEN periods.st AND periods.en
              ORDER BY time ASC
              LIMIT 1
             )