Postgres -慢查询

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条记录，但impression和play都有几百万条记录。我在所有3个表上创建了包含上述所有列(包括每个列中的唯一id列)的索引，但不确定是否有更好的方法来处理这些索引，或者是否有更好的方法来编写查询。

Answer 1

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

您可以尝试一些表面上的事情，这可能不会影响查询计划：

对于日期，impression.DATE BETWEEN '2017-12-01' AND '2017-12-31'

• Use使用BETWEEN运算符:用ROW_NUMBER(OVER ...)之类的窗口函数代替

更新

表分区示例(摘自手册)：

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上有索引。