PSQL 请求太慢。如何修复它？

Question

SELECT 
    COUNT(a)/COUNT(s)*100 as aratio, 
    COUNT(b)/COUNT(s)*100 as bratio, 
    COUNT(c)/COUNT(s)*100 as cratio, 
    COUNT(a), 
    COUNT(b), 
    COUNT(c), 
    COUNT(s) 
FROM 
    (SELECT COUNT(cid) as a FROM images WHERE width > height AND category_id = 4 GROUP BY cid) as aq, 
    (SELECT COUNT(cid) as b FROM images WHERE width < height AND category_id = 4 GROUP BY cid) as bq, 
    (SELECT COUNT(cid) as c FROM images WHERE width = height AND category_id = 4 GROUP BY cid) as cq, 
    (SELECT COUNT(cid) as s FROM images WHERE category_id = 4 GROUP BY cid) as sq;

我怎样才能使这个请求更有效？

Answer 1

使用 WITH 是可能的。将所有查询移至 WITH 并进行一些修改：将 COUNT(cid) 更改为 COUNT(DISTINCT cid) 并删除 GROUP BY 子句根本没有。

Answer 2

您可以使用类似这样的内容：

SELECT 
    SUM(CASE (width > height) WHEN true THEN 1 ELSE 0 END)::float8*100/COUNT(*) as aratio, 
    SUM(CASE (width < height) WHEN true THEN 1 ELSE 0 END)::float8*100/COUNT(*) as bratio, 
    SUM(CASE (width = height) WHEN true THEN 1 ELSE 0 END)::float8*100/COUNT(*) as cratio, 
    SUM(CASE (width > height) WHEN true THEN 1 ELSE 0 END), 
    SUM(CASE (width < height) WHEN true THEN 1 ELSE 0 END), 
    SUM(CASE (width = height) WHEN true THEN 1 ELSE 0 END), 
    COUNT(*) 
FROM 
    images WHERE category_id = 4;

此查询不按 cid 分组，但可能您不需要它。