我的目标是创建一个查询,该查询将返回在365天窗口中购买的唯一客户的计数。我在postgres中创建了下面的查询,结果查询非常慢。我的表是812024行的just order dates和customer id。当我删除distinct语句时,我可以让查询在大约60秒内返回结果,使用它,我还没有完成。我在(订单日期,id)创建了一个索引。我是一个完全不懂SQL的新手,这真的是我第一次用它做任何事情,在试图找到一个解决这个问题的方法一整天之后,我找不到任何我可以去工作的东西,尽管我已经看到了很多关于distinct的慢性能。
SELECT
(d1.Ordered) AS Ordered,
COUNT(distinct d2.ID) Users
FROM
(
SELECT order_date AS Ordered
FROM orders
GROUP BY order_date
) d1
INNER JOIN
(
SELECT order_date AS Ordered, id
FROM orders
) d2
ON d2.Ordered BETWEEN d1.Ordered - 364 AND d1.Ordered
GROUP BY d1.Ordered
ORDER BY d1.Ordered
"Sort (cost=3541596.30..3541596.80 rows=200 width=29)"
" Sort Key: orders_1.order_date"
" -> HashAggregate (cost=3541586.66..3541588.66 rows=200 width=29)"
" -> Nested Loop (cost=16121.73..3040838.52 rows=100149627 width=29)"
" -> HashAggregate (cost=16121.30..16132.40 rows=1110 width=4)"
" -> Seq Scan on orders orders_1 (cost=0.00..14091.24 rows=812024 width=4)"
" -> Index Only Scan using x on orders (cost=0.43..1822.70 rows=90225 width=29)"
" Index Cond: ((order_date >= (orders_1.order_date - 364)) AND (order_date <= orders_1.order_date))"
最佳答案
假设实际的date
类型。
SELECT d.day, count(distinct o.id) AS users_past_year
FROM (
SELECT generate_series(min(order_date), max(order_date), '1 day')::date AS day
FROM orders -- single query
) d
LEFT JOIN ( -- fold duplicates on same day right away
SELECT id, order_date
FROM orders
GROUP BY 1,2
) o ON o.order_date > d.day - interval '1 year' -- exclude
AND o.order_date <= d.day -- include
GROUP BY 1
ORDER BY 1;
如果这是一件普通的事情,那么在同一天首先从同一个用户那里折叠多个购买的东西才有意义。否则,省略该步骤并简单地将join留在表
orders
中会更快。很奇怪
orders.id
会是用户的ID。应该命名为类似于user_id
。如果您对
generate_series()
列表中的SELECT
不满意(这很好),可以在Postgres9.3+中将其替换为LATERAL JOIN
。FROM (SELECT min(order_date) AS a
, max(order_date) AS z FROM orders) x
, generate_series(x.a, x.z, '1 day') AS d(day)
LEFT JOIN ...
注意,在本例中
day
是timestamp
类型。工作原理相同。你可能想投。一般性能提示
我知道这是一个只读表,供单个用户使用。这简化了事情。
你似乎已经有了一个索引:
CREATE INDEX orders_mult_idx ON orders (order_date, id);
很好。
一些尝试:
基础
当然,通常的性能建议适用于:
https://wiki.postgresql.org/wiki/Slow_Query_Questions
https://wiki.postgresql.org/wiki/Performance_Optimization
流线型表
使用此索引对表进行一次群集:
CLUSTER orders USING orders_mult_idx;
这应该有点帮助。它还有效地在表上运行
VACUUM FULL
,这将删除任何死行并压缩表(如果适用)。更好的统计
ALTER TABLE orders ALTER COLUMN number SET STATISTICS 1000;
ANALYZE orders;
解释如下:
Configuration parameter work_mem in PostgreSQL on Linux
分配更多RAM
确保你有足够的资源分配。尤其是对于
shared_buffers
and work_mem
。您可以在会话期间临时执行此操作。试验planner methods
尝试禁用嵌套循环(
enable_nestloop
)(仅在会话中)。也许散列连接更快。(不过,我会很惊讶的。)SET enable_nestedloop = off;
-- test ...
RESET enable_nestedloop;
临时表
由于这看起来是一个“临时表”的性质,您可以尝试使它成为一个实际的临时表保存在内存中。你需要足够的内存来分配足够的
temp_buffers
。详细说明:How to delete duplicate entries?
确保手动运行
ANALYZE
。自动真空不包括温度表。关于sql - 在Postgres中使用计数不同的慢查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/25026079/