计数查询PostgreSQL的效率，物化视图 [英] PostgreSQL efficiency of count query, materialized views

问题描述

可能重复：结果
  <一href=\"http://stackoverflow.com/questions/13075210/optimization-of-count-query-for-postgresql\">Optimization PostgreSQL的计数查询

使用的PostgreSQL 9.2，我们试图找出是否有跟踪结果的查询的数量，并以有效的方式返回该号码的方法。该查询应该执行数（可能是几十到几百甚至上千个）次每秒。我们现在所拥有的查询看起来是这样，但我们不知道这是无效的：

Using PostgreSQL 9.2, we are trying to figure out if there is a way to keep track of the number of results for a query, and return that number in an efficient manner. This query should be executed several (possibly tens to hundreds or even thousands) of times per second. The query we have right now looks like this, but we wonder if this is inefficient:

-- Get # of rows that do not have ‘parameter value’ in array_column
select count(*) 
    from table
    where not (ARRAY[‘parameter value’] <@ table.array_column)

我的问题是（答案可能会解决同时存在多个问题）：

My questions are (an answer might solve multiple problems at the same time):

时的计数（ID）（或COUNT（*）为此事）该查询线性（O（n））的查询？

Is the count(id) (or count(*) for that matter) for that query a linear (O(n)) query?

有没有一些方法，使这项查询在PostgreSQL中更有效率？请记住，我们需要查询不同的参数值，所以我相信保持物化视图，因为这是不可行的（虽然，如果被认为是更好的，我们可以考虑创建一个用于每个参数值）。

Is there some way to make this query more efficient in PostgreSQL? Please keep in mind that we need to query for different parameter values, so I believe keeping a materialized view for it is not feasible (although, we might consider creating one for each parameter value if that is considered to be better).

有任何改变，我应该作出查询，数据库结构还是我的PostgreSQL服务器，可以帮助我提高查询性能配置？

Is there any change I should make to the query, database structure or the configuration of my PostgreSQL server that might help me improve the query performance?

任何指针或建议将大大AP preciated。如果这是一个完全错误的方式做到这一点，请让我知道。非常感谢！

Any pointers or suggestions will be greatly appreciated. If this is a completely wrong way to do this, please let me know. Thanks very much!

修改

考虑到什么回答，我在想，如果这将是合理使用物化视图。我的意思是有几个物化视图（每一个不同的参数值，具有其中在该值不在present行）。我们的参数值，在一定程度上，predictable，所以这似乎并没有出过远有作为解决方案。这带来了另外一个问题提出了质疑：会物化视图帮助这里？是有一些限制（在定义或绩效）作为物化视图（或表）的数量，我可以在数据库中创建？

Taking into consideration what was answered, I was wondering if it would be plausible to use materialized views. By this I mean having several materialized views (each one for a different parameter value, having the rows where that value is not present). We the parameter values are, to a certain extent, predictable, so this doesn't seem too far out there as a solution. This brings another matter into question: Would materialized views help here? Is there some limitation (either in definition or performance) as to the number of materialized views (or tables) that I can create in a database?

非常感谢您的帮助！

推荐答案

首先想到的是缓存的数值。第一个想法

The first idea that come to mind is to cache the value.

您应该评估该值的变化率，根据所决定，如果你想有一个触发器，当这个表被更新来计算新的价值和它缓存起来执行。

You should evaluate the rate of change of this value, and depending on that decide if you want to have a trigger to be executed when this table is updated to compute the new value and cache it somewhere.

该值的查询结果将是一个简单的SELECT没有任何WHERE子句，使得它非常快的。

The resulting query for that value would be a simple SELECT without any WHERE clause, making it very fast.

或者你可以简单地做了变化，之前和之后得到一些统计数据就知道，如果你已经在速度上获得的。

Or you could simply do the change, and get some stats before and after to know if you've gained in speed.

请参阅有进一步的解释的。

这篇关于计数查询PostgreSQL的效率，物化视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！