sql - 慢速 SQL 事务从 Postgres 中的表中获取最新的时间戳行

Question

我在 Postgres 中尝试检索买多于卖的目录产品的最新价格时遇到了缓慢的交易问题。此时它是一个相当大的表，超过 200 万行。我有这个用于历史目的。我目前使用的是:

select * from ta_price a
  join (
   select catalogproduct_id, max(timestamp) ts
     from ta_price
    group by catalogproduct_id
       ) b on a.catalogproduct_id = b.catalogproduct_id
          and a.timestamp = b.ts
          AND buy > sell;

catalogproduct_id 是 catalogproduct 表的外键。

在总共 2201760 行中，它选择了 2296 行。总运行时间为 181,792.705 毫秒。

关于如何改进这一点的任何见解？

编辑:

我被所有的答案震惊了!我还想在 Django ORM 的领域下更多地限定这个问题。我正在努力在此表上合并一个复合键(或类似键)(使用 catalogproduct_id 和时间戳)。我有一个主键，它是一个自动递增索引，我想这和根本没有索引一样好。

编辑 2:添加@Erwin 建议的部分索引后，在 ta_price(catalogproduct_id，时间戳)上创建索引 my_partial_idx
WHERE buy > sell;，我使用来自@wildplasser 的查询大约需要 10-12 秒的查询时间。为了进一步说明，我的表格是一段时间内产品价格(买卖)的快照。在任何给定时间，我想知道当前(截至其最新快照时间)哪些产品有buy > sell。

Answer 1

经过深思熟虑修改后的答案

SELECT *
  FROM ta_price a
  JOIN (
   SELECT catalogproduct_id, max(timestamp) ts
     FROM ta_price
    GROUP BY catalogproduct_id
        ) b ON a.catalogproduct_id = b.catalogproduct_id
           AND a.timestamp = b.ts
           AND a.buy > a.sell;

buy和 sell不符合你的问题。取决于 buy > sell 的选择性您可以通过添加相同的 WHERE 来加快查询速度- 子选择的子句。但是，这会产生不同的结果。我偶然添加它，您可能会忽略它:

SELECT *
  FROM ta_price a
  JOIN (
   SELECT catalogproduct_id, max(timestamp) ts
     FROM ta_price
    WHERE buy > sell
    GROUP BY catalogproduct_id
        ) b ON a.catalogproduct_id = b.catalogproduct_id
           AND a.timestamp = b.ts
 WHERE a.buy > a.sell;

无论哪种方式，像@Will 暗示的简单索引都会有所帮助:
CREATE INDEX my_idx ON ta_price (catalogproduct_id, timestamp);

不过，还有一个更好的方法。
无条件max()在 subselect 中将导致顺序表扫描，而不管索引如何。对于 2.2m 行，这样的操作永远不会很快。
JOIN条件，结合 WHERE外层子句SELECT , 将从上述指数中获利。取决于 buy > sell 的选择性一个partial index在磁盘和 RAM 中会快一点或快很多，相应地，在磁盘和 RAM 中也会更小:

CREATE INDEX my_partial_idx ON ta_price (catalogproduct_id, timestamp)
 WHERE buy > sell;

在这种情况下，索引中列的顺序无关紧要。它还将加快我的查询的第二个变体。

您提到该表是用于“历史”目的？如果这意味着没有新数据，您可以使用实体化 View 大大加快处理速度。

---

旁注:我不会使用 timestamp作为列名。它在 PostgreSQL 中是允许的，但它是一个 reserved word在所有 SQL 标准中。

---

好的，要紧的事放在最后:对于一个 220 万行的表，您需要多的资源，而不是 postgres 开箱即用的资源。

• 查看您的 postgresql.conf 文件并检查 shared_buffers 的设置和 work_mem开始。
• 咨询 postgres wiki for performance tuning
• 在 ressource consumption 上查阅精细手册
• 在 planner costs 上查阅精细手册

• 增加这些统计设置:
  ALTER TABLE tmp.ta_price ALTER COLUMN buy SET STATISTICS 1000;<br/>
ALTER TABLE tmp.ta_price ALTER COLUMN sell SET STATISTICS 1000;<br/>
ALTER TABLE tmp.ta_price ALTER COLUMN ts SET STATISTICS 1000;

  然后运行ANALYZE tmp.ta_price;

• 确保autovacuum在跑。如有疑问，请运行 VACUUM ANALYZE ta_price看看有没有效果。

  ---

我在资源有限的 pg 8.4 安装上玩过 wildplasser 的测试设置(非常有帮助!)。以下是来自 EXPLAIN ANYLYZE 的总运行时间

Erwin 1)        901.487 ms  wildplasser 1) 1148.045 ms  A.H.           2922.113 ms  

带有附加(买入 > 卖出)子句的变体 2:

Erwin 2)        536.678 ms  wildplasser 2)  809.215 ms  

部分索引:

Erwin 1)       1166.793 ms  -- slower (!), than unexpected

_{可能规划器成本已经关闭，这个测试数据库集群针对主数据库进行了优化有更多的资源。}

wildplasser 1) 1122.609 ms -- rest is faster as expected  Erwin 2)        481.487 ms  wildplasser 2)  769.887 ms  

简历

A.H. 的版本需要更长的时间(与您报告的结果相同)。窗口函数往往很慢，尤其是在旧版本的 postgres 上。我的替代查询速度是预期的两倍。问题是，如果需要不同的结果 - 也许不需要。

无论如何，那是 30 万行。在 5 年前的服务器上，在资源有限(但主要是正确设置)的 8.4 版上，查询需要 0.5 - 1 秒。如果机器和设置都不错(足够的 RAM!)，您应该至少将其降低到10 秒以下。