我正在编写一个必须多次执行相同查询的应用程序。该查询有一个(可能较大的)数组作为参数,如下所示:
SELECT
m.a, SUM(m.b) as b, SUM(m.c) as c, SUM(m.d) as d
FROM table_m m JOIN table_k k ON (k.x IN %s AND k.id = m.y)
WHERE m.b > 0
GROUP BY m.a我在PostgreSQL9.1上使用Psycopg2。对于每个查询,我创建一个新的游标并执行()以数字列表作为参数的查询(该查询在我的测试强制转换中执行了大约5000次)。输入列表的长度从1到5000项不等。
平均而言,该查询运行所需的时间略低于50 On,最慢的执行时间约为500 On。
我对此有两个问题:
table_k模式
Column | Type | Modifiers
---------------+--------+-----------
id | bigint | not null
x | bigint |
Indexes:
"table_k_pkey" PRIMARY KEY, btree (id)
"table_k_id_x_idx" btree (id, x)
"table_k_x_idx" btree (x)table_m模式
Column | Type | Modifiers
---------------------+-----------------------------+-----------
id | bigint | not null
y | bigint |
a | bigint |
b | integer |
c | integer |
d | double precision |
Indexes:
"table_m_pkey" PRIMARY KEY, btree (id)
"table_m_y_idx" hash (y)
"table_m_a_idx" btree (a)
"table_m_b_idx" btree (b)希望这是足够的信息。
发布于 2013-01-26 11:47:10
可能优化器并不是很聪明,而且评估IN的次数比您希望的要多。尝试将其移到子查询中:
SELECT
m.a, SUM(m.b) as b, SUM(m.c) as c, SUM(m.d) as d
FROM table_m m
JOIN
(
SELECT *
FROM table_k
WHERE x IN %s
) k ON k.id = m.y
WHERE m.b > 0
GROUP BY m.a如果首先使用长列表的IN,您还可能获得缓慢的性能。可以尝试创建临时表,插入要搜索的值,然后加入临时表。
发布于 2013-01-30 07:15:06
EXPLAIN (analyze)输出。我看不出查询现在的样子有什么关键。准备好的语句是可用于优化性能的服务器端对象.执行准备语句时,将解析、分析和重写指定的语句。当随后发出EXECUTE命令时,计划并执行准备好的语句。这种分工避免了重复的解析分析工作,同时允许执行计划依赖于所提供的特定参数值。
请注意(在文档中也提到过),您不会从PREPAREing小语句中获得多少利润,因为解析、分析和重写步骤所花费的时间比实际执行查询的时间要少得多。
另外,如果您的查询由不同的会话执行,那么性能将不会提高。因为PostgreSQL没有跨会话共享预追加语句的基础结构。
如果您经常执行您的查询,并且您确实使用相同的输入来执行这些查询,那么我建议您在应用程序或某些专用数据库表中组织结果的缓存。您的数组似乎是缓存查找的完美键。
https://stackoverflow.com/questions/14536585
复制相似问题