代码之家 › 专栏 › 技术社区 › EMP

在视图中封装Postgres查询使其速度非常慢

window-functions view postgresql performance

EMP · 技术社区 · 14 年前

我有一个查询在Postgres8.4上运行大约5秒钟。它从联接到其他一些表的视图中选择数据,但也使用 小精灵 窗口功能,即

SELECT *, lag(column1) OVER (PARTITION BY key1 ORDER BY ...), lag(...)
FROM view1 v
JOIN othertables USING (...)
WHERE ...

为了方便起见,我创建了一个新的视图,

SELECT *, lag(column1) OVER (PARTITION BY key1 ORDER BY ...), lag(...)
FROM view1 v

然后从中选择,使用前面提到的所有其他联接和过滤器。令我惊讶的是,这个查询在12分钟内没有完成(当时我停止了它)。显然,Postgres选择了不同的执行计划。我如何才能让它不这样做,即使用与原始查询相同的计划?我本以为一个观点不应该改变执行计划,但显然它改变了。

编辑:更重要的是,我发现即使我把第一个视图的内容复制到第二个视图中, 仍然不会回来。

编辑2:好的,我已经充分简化了查询,以便发布计划。

使用视图(在任何合理时间内都不会返回):

Subquery Scan sp  (cost=5415201.23..5892463.97 rows=88382 width=370)
  Filter: (((sp.ticker)::text ~~ 'Some Ticker'::text) AND (sp.price_date >= '2010-06-01'::date))
  ->  WindowAgg  (cost=5415201.23..5680347.20 rows=53029193 width=129)
        ->  Sort  (cost=5415201.23..5441715.83 rows=53029193 width=129)
              Sort Key: sp.stock_id, sp.price_date
              ->  Hash Join  (cost=847.87..1465139.61 rows=53029193 width=129)
                    Hash Cond: (sp.stock_id = s.stock_id)
                    ->  Seq Scan on stock_prices sp  (cost=0.00..1079829.20 rows=53029401 width=115)
                    ->  Hash  (cost=744.56..744.56 rows=29519 width=18)
                          ->  Seq Scan on stocks s  (cost=0.00..744.56 rows=29519 width=18)

将window函数从视图中取出并放入查询本身(这将立即返回):

WindowAgg  (cost=34.91..34.95 rows=7 width=129)
  ->  Sort  (cost=34.91..34.92 rows=7 width=129)
        Sort Key: sp.stock_id, sp.price_date
        ->  Nested Loop  (cost=0.00..34.89 rows=7 width=129)
              ->  Index Scan using stocks_ticker_unique on stocks s  (cost=0.00..4.06 rows=1 width=18)
                    Index Cond: ((ticker)::text = 'Some Ticker'::text)
                    Filter: ((ticker)::text ~~ 'Some Ticker'::text)
              ->  Index Scan using stock_prices_id_date_idx on stock_prices sp  (cost=0.00..30.79 rows=14 width=115)
                    Index Cond: ((sp.stock_id = s.stock_id) AND (sp.price_date >= '2010-06-01'::date))

因此,在缓慢的情况下,它似乎试图先将window函数应用于所有数据,然后对其进行过滤,这可能是问题所在。但我不知道为什么会这样。

2 回复 | 直到 11 年前

Denis de Bernardy 14 年前

你在这两个计划之间的区别来自于合并。这将阻止使用嵌套循环计划。当你在你的视图中使用聚合时,你把自己放在一个不利的场景中。

例如,这几乎总是会导致两个表上的合并或哈希联接计划,然后是一个前N排序:

select foo.*
from foo
join (select bar.* from bar group by bar.field) as bar on foo.field = bar.field
where ...
order by bar.field
limit 10;

Community CDub 8 年前

也许你可以考虑使用 Common Table Expression (CTE) 而不是视图。我可以用与使用视图类似的方式帮助使查询更清晰,但似乎不会以同样的方式影响执行计划。

我也有类似的问题 this question 并且使用CTE而不是视图使执行计划更加高效。