我在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行。总运行时间为181792.705 ms。
对如何改进有什么见解吗？
编辑：
我被所有的答案惊呆了！我还想在Django ORM的范围内对这个问题进行更多的限定。我很难在这个表上合并一个复合键(或类似的键((使用catalogproduct_id和timestamp(。我有一个主键，它是一个自动递增的索引，我想这和根本没有索引一样好。
编辑2:在添加了@Erwin建议的部分索引后，CREATE INDEX my_partial_idx ON ta_price (catalogproduct_id, timestamp)
WHERE buy > sell;，我使用@wildplasser的查询大约10-12秒的查询时间。为了进一步澄清，我的表格是一段时间内产品价格(买入和卖出(的快照。在任何给定的时间，我想知道当前(截至其最新快照时间(哪些产品具有buy > sell。
经过一些考虑后修改了答案
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的选择性，您可以通过在子select中添加相同的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，时间戳(
不过，有一种更优越的方法
子选择中的无条件max()将导致不考虑索引的顺序表扫描。对于2.2米的行，这样的操作永远不会很快JOIN条件与外部SELECT的WHERE子句相结合，将从类似于上面的索引中获利。根据buy > sell的选择性，部分索引在光盘和RAM:中会稍快或明显更快，相应地会更小
CREATE INDEX my_partial_idx ON ta_price (catalogproduct_id, timestamp)
WHERE buy > sell;
在这种情况下，索引中列的顺序无关紧要。它还将加速ab我的第二个查询变体。
你提到这张桌子是为了"历史"目的？如果这意味着没有新的数据，那么使用物化视图可以大大加快速度。
附带说明：我不会使用timestamp作为列名。它在PostgreSQL中是允许的，但在所有SQL标准中都是保留字。
好的，第一件事最后：对于一个220万行的表，您需要比postgres开箱即用的资源多得多的资源。
- 首先查看postgresql.conf文件并检查
shared_buffers和work_mem的设置 - 有关性能调整，请参阅postgres wiki
- 查阅有关资源消耗的精细手册
- 查阅规划师成本精细手册
增加这些统计设置：
ALTER TABLE tmp.ta_price ALTER COLUMN buy SET STATISTICS 1000;
ALTER TABLE tmp.ta_price ALTER COLUMN sell SET STATISTICS 1000;
ALTER TABLE tmp.ta_price ALTER COLUMN ts SET STATISTICS 1000;然后运行
ANALYZE tmp.ta_price;确保自动真空吸尘器正在运行。如果有疑问，请运行
VACUUM ANALYZE ta_price，看看它是否有效果。
我在资源有限的第8.4页安装上玩过wildplaser的测试设置(非常有用！(。以下是EXPLAIN ANYLYZE的总运行时间
欧文1(901.487毫秒wildplaser 1(1148.045毫秒A.H.2922.113毫秒
附加(买入>卖出(条款的变体2：
欧文2(536.678毫秒wildplaser 2(809.215毫秒
带部分索引：
欧文1(1166.793毫秒——比出乎意料的慢(！(
可能规划器成本降低了，这个测试数据库集群针对主数据库进行了优化拥有更多的资源
wildplaser 1(1122.609毫秒——休息时间比预期更快欧文2(481.487毫秒wildplaser 2(769.887毫秒
简历
A.H.的版本需要更长的时间(和您报告的结果相同(。窗口函数往往很慢，尤其是在旧版本的postgres上。我的备选查询速度是预期的两倍。问题是，如果想要不同的结果——也许不是。
总之，那是30万行。在8.4版本上，查询耗时0.5-1秒，在5年历史的服务器上资源有限(但大多设置正确(。有了一台不错的机器和不错的设置(足够的RAM！(，你应该至少将其降低到低于10s。
SELECT * from ta_price a
WHERE NOT EXISTS (
SELECT *
FROM ta_price b
WHERE b.catalogproduct_id = a.catalogproduct_id
AND b.timestamp > a.timestamp
-- AND b.buy > b.sell -- Not clear if OP wants this
)
AND a.buy > a.sell
;
(catalogproduct_id，timestamp(上的索引可能是有益的。子查询中可能需要额外的条件"AND b.buy>b.sell"(OP中的文本不清楚实际需要什么(。
更新："timestamp"是一个保留字。我把它改了一点。还添加了测试数据。
DROP SCHEMA tmp cascade;
CREATE SCHEMA tmp ;
CREATE TABLE tmp.ta_price
( catalogproduct_id INTEGER NOT NULL
, tttimestamp timestamp NOT NULL
, buy DECIMAL (10,2)
, sell DECIMAL (10,2)
);
INSERT INTO tmp.ta_price(catalogproduct_id,tttimestamp,buy,sell)
SELECT serie_n
, serie_t
, serie_v + ((100* random()) - 30)
, serie_v + ((100* random()) - 20)
FROM generate_series (1,10000) serie_n
, generate_series ( '2011-09-01 00:00:00' , '2011-10-01 00:00:00' , '1 day' ::interval) serie_t
, generate_series ( 100 , 100 ) serie_v
;
DELETE FROM tmp.ta_price WHERE random() < 0.02;
CREATE INDEX tmptmp ON tmp.ta_price (catalogproduct_id,tttimestamp);
-- there may be some duplicate records: clear them
DELETE FROM tmp.ta_price a
WHERE EXISTS (SELECT * FROM tmp.ta_price b
WHERE b.catalogproduct_id = a.catalogproduct_id
AND b.tttimestamp = a.tttimestamp
AND b.ctid > a.ctid
);
DROP INDEX tmp.tmptmp ;
ALTER TABLE tmp.ta_price
ADD PRIMARY KEY (catalogproduct_id,tttimestamp)
;
EXPLAIN ANALYZE
SELECT * from tmp.ta_price a
WHERE NOT EXISTS (
SELECT *
FROM tmp.ta_price b
WHERE b.catalogproduct_id = a.catalogproduct_id
AND b.tttimestamp > a.tttimestamp
-- AND b.buy > b.sell -- Not clear if OP wants this
)
AND a.buy > a.sell
;
查询计划：(针对ta_price中的30万条记录(
------------------------------------------------------------------------------
Nested Loop Anti Join (cost=0.00..8607.82 rows=67508 width=38) (actual time=457.486..482.943 rows=4052 loops=1)
-> Seq Scan on ta_price a (cost=0.00..6381.34 rows=101262 width=38) (actual time=0.027..80.256 rows=123142 loops=1)
Filter: (buy > sell)
-> Index Scan using ta_price_pkey on ta_price b (cost=0.00..10.57 rows=506 width=12) (actual time=0.003..0.003 rows=1 loops=123142)
Index Cond: ((b.catalogproduct_id = a.catalogproduct_id) AND (b.tttimestamp > a.tttimestamp))
Total runtime: 483.325 ms
(6 rows)
我会尝试在catalogproduct_id和时间戳上添加一个索引。在我看来，这是在扫描表格，没有其他信息。
您的查询将把表连接到自身，以获得最大值。你可能会尝试使用"窗口函数"来防止这种情况——也许它们在你的情况下效果更好：
SELECT * FROM (
SELECT *, rank() OVER w
FROM ta_price
WINDOW w AS (PARTITION BY catalogproduct_id ORDER BY timestamp DESC)
) c WHERE c.rank = 1 AND c.buy > c.sell;