我的目标是创建一个查询,该查询将返回在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 ...

注意,在本例中daytimestamp类型。工作原理相同。你可能想投。
一般性能提示
我知道这是一个只读表,供单个用户使用。这简化了事情。
你似乎已经有了一个索引:
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/

10-16 17:32
查看更多