查询优化

Question

我在两个结构完全相同的数据库中测试了以下查询，在第一个数据库中，有400万个条目，它在33秒钟内返回了结果。第二个表有2900万行，从执行查询到现在已经有16个小时了，我还没有得到返回。

SELECT sbvpip*4 as smallbvpip,btnvpip*4 as buttonvpip, sum(amt_won)*400/count(*) AS winrate, count(*) as count

FROM holdem_hand_player_statistics

    JOIN (

    SELECT id_player AS pid2, id_hand AS hid, sbvpip
    FROM holdem_hand_player_statistics

        JOIN (
        SELECT id_player AS pid, ROUND(avg(flg_vpip::int)*25) AS sbvpip
        FROM holdem_hand_player_statistics
        WHERE position = 8 AND cnt_players = 6
        GROUP BY id_player
        ) AS auxtable
        ON pid = id_player

    WHERE position = 8 AND cnt_players = 6
    ) AS auxtable2
    ON hid = id_hand


    JOIN (

    SELECT id_player AS pid4, id_hand AS hid2, btnvpip
    FROM holdem_hand_player_statistics

        JOIN (
        SELECT id_player AS pid3, ROUND(avg(flg_vpip::int)*25) AS btnvpip
        FROM holdem_hand_player_statistics
        WHERE position = 0 AND cnt_players = 6
        GROUP BY id_player
        ) AS auxtable3
        ON pid3 = id_player

    WHERE position = 0 AND cnt_players = 6
    ) AS auxtable4
    ON hid2 = id_hand


WHERE POSITION = 0 and cnt_players = 6



GROUP BY sbvpip,btnvpip
ORDER BY 1,2;

，我能做些什么使这个查询执行得更快?

表是否可能已损坏或类似的情况？一张桌子比另一张大7~8倍，但它需要15000倍的时间来处理，这正常吗？

欢迎任何其他评论！

如果我的英语不清楚，就让我知道我会尝试用另一种方式表达自己。

非常感谢你的帮助，

附加信息：

从我使用的变量中，有3个是索引: id_hand、id_player、position。主键是(id_hand，id_player)。该表共有129列和6个索引。

我还在两个表中运行了“解释”，得到了不同的结果。这两个结果都出现在gdocs电子表格上：https://spreadsheets.google.com/ccc?key=tGxqxVNzHYznb1VVjtKyAuw&authkey=CJ-BiYkN&authkey=CJ-BiYkN#gid=0

Answer 1

可能您正在为更多行使用更多的排序内存:您的work_mem设置是什么？与buffercache类似，因为您要多次扫描同一个表，所以将行插入缓存可能是至关重要的。

另外，您应该重新检查该查询，并尝试找到不必多次将statistics表重新加入到自身上的方法。如果没有一些小的测试数据和预期的输出，很难提供建议。您使用的是哪个版本的PostgreSQL？使用8.4，您至少可以从单个CTE获得辅助和auxtable3。

Answer 2

我认为索引在其中一个服务器上是不存在的或不正确的。

也可能是阻塞阻止了查询的完成。尤其是如果有一个未完成的交易坐在那里。

Answer 3

查询看起来很好。为了提高性能，尝试做像@HLGEM这样的索引。还要尝试执行每个单独的子查询，以查看哪个子查询的性能很低。