查询优化

Question

这就是我当前的查询，它可以工作，但速度很慢：

    SELECT row, MIN(flg) ||' to ' ||Max (flg) as xyz , avg(amt_won), count(*)
FROM(
SELECT (ROW_NUMBER() OVER (ORDER BY flg))*100/
(SELECT  count(*)+100 as temprow FROM temporary_six_max) as row, flg, amt_won
FROM temporary_six_max 
    JOIN (
    SELECT id_player AS pid,  avg(flg_vpip::int)  AS flg 
    FROM temporary_six_max
    GROUP BY id_player 
    ) AS auxtable
    ON pid = id_player
) as auxtable2
group by 1
order by 1;

我在固定(或几乎固定)计数100个范围中分组，这些范围按平均(Flg_vpip)排序，按id_player分组。

我在这里粘贴了结果，以防有助于理解：https://spreadsheets0.google.com/ccc?key=tFVsxkWVn4fMWYBxxGYokwQ&authkey=CNDvuOcG&authkey=CNDvuOcG#gid=0

我想知道在这种情况下是否有比ROW_NUMBER()更好的函数可用，我觉得我做了太多的子选择，但我不知道如何优化它。

如果有任何帮助，我将不胜感激。

如果有什么不清楚的地方，请告诉我。

谢谢。

编辑：

我创建auxtable 2的原因是因为当我使用(ROW_NUMBER() OVER ()OVER (ORDER BY flg)，并使用其他聚集命令，如avg(amt_won)和count(*)，这是必不可少的)，我得到一个错误，说flg应该在聚合函数中，但我不能按flg的聚合函数排序。

Answer 1

我生成了一些用于测试的数据，如下所示：

create table temporary_six_max as
select id_player, flg_vpip,
       random()*100 * (case flg_vpip when 0 then 1 else -1 end) as amt_won
from (select (random()*1000)::int as id_player, random()::int as flg_vpip
      from generate_series(1,1000000)) source;
create index on temporary_six_max(id_player);

您的查询在此基础上成功运行，但并没有生成完全相同的计划，我在init- plan中得到了一个嵌套循环，而不是merge和seq扫描--希望您没有关闭enable_seqscan？

一种只使用一次表扫描的解决方案：

select row, min(flg) || ' to ' || max(flg) as xyz, avg(amt_won), count(*)
from (select flg, amt_won, ntile(100) over(order by flg) as row
      from (select id_player as pid, amt_won,
                   avg(flg_vpip::int) over (partition by id_player) as flg
            from temporary_six_max
           ) player_stats
     ) chunks
group by 1
order by 1

坏消息是，这实际上在我的机器上表现得更差，特别是如果我提升了足够的work_mem来避免第一次磁盘排序(生成player_stats，按flg排序)。虽然增加work_mem确实减少了一半的查询时间，所以我猜这至少是一个开始？

话虽如此，我的查询运行了大约5秒钟来处理temporary_six_max中的10E6输入行，这比您发布的快了一个数量级。您的表是否适合您的缓冲区缓存？如果没有，单次扫描解决方案可能对您更好。(您使用的是哪个版本的Postgresql？“解释(分析打开，缓冲区打开) select...”将显示9.0中的缓冲区命中率/未命中率，或者只查看您的"shared_buffers“设置并与表大小进行比较)