Postgres - Slow Query

Question

在让查询完成执行时遇到一些麻烦 - 它运行，运行，运行，我对索引和查询性能没有足够深入的了解，不知道如何调整它以加快其执行速度。查询如下(它说明了我想要的最终结果(：

SELECT
  device.network, device.name AS device, device.mac,
  play.advertiserid, play.filename, play.startdate::timestamp at time zone device.timezone as filestartdate,
  impression.date, impression.views
FROM impression
INNER JOIN device ON
  impression.mac = device.mac
INNER JOIN play ON
  impression.date >= play.startdate::timestamp at time zone device.timezone AND
  impression.date < ((play.startdate::timestamp at time zone device.timezone) + play.spotrunlength * interval '1 second') AND
  play.devicename = device.name
WHERE
  impression.date >= '2017-12-01' AND
  impression.date < '2017-12-31'
ORDER BY impression.date ASC
LIMIT 100;

设备表只有大约 100 条记录，但印象和播放都有几百万条记录。我在包含上述所有列的所有 3 个表上创建了索引(每个列中都有一个唯一的 id 列(，但不确定是否有更好的方法来处理这些索引，或者是否有更好的方法来编写该查询。

Answer 1

我不知道

您是否有权修改表结构，但是如果您这样做，您可以尝试在日期列上添加表分区(Postgres 10+(。 这应该会加快基于日期的这些表的任何连接/搜索条件。

您可以尝试一些装饰性的东西，它们可能不会影响查询计划：

1. 对日期使用 BETWEEN 运算符：impression.DATE BETWEEN '2017-12-01' AND '2017-12-31'

2. 使用窗口函数(如 ROW_NUMBER(OVER ...) 而不是LIMIT 100

更新
表分区示例(来自手册(：

CREATE TABLE measurement (
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

这将允许您加快指定logdate的范围查询，例如WHERE logdate BETWEEN XXX AND XXX

更复杂的示例(来自手册(：

CREATE TABLE measurement_year_month (
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));

查看一些链接：

分区：https://www.postgresql.org/docs/10/static/ddl-partitioning.html
创建表：https://www.postgresql.org/docs/10/static/sql-createtable.html

Answer 2

我会

说你的数据模型有问题。

加入如下条件：

impression.date >= play.startdate::timestamp at time zone device.timezone

可能只能使用嵌套循环连接进行处理，并且条件无法很好地索引。

与其存储本地日期和时间戳并在查询时处理时区，不如将所有事件存储为 timestamp with time zone ，即 UTC 时间戳。

然后，您的查询应该变得更加简单，并且可能可以使用更有效的哈希或合并联接。

可以将时区信息保留在数据中，但只能将其用于显示目的。

除此之外，请确保有一个索引 impression.date .