主页 / dba / 问题 / 297892
Accepted
Harrison Lievesley
Asked: 2021-08-12 04:50:36 +0800 CST

使用子选择时查询慢

  • 772

我创建了一个查询，该查询可以返回具有 YTD、去年、前一年和前一年的不同列的特定组的所有收入。

它的工作原理非常缓慢，而且由于我使用的子查询，我可以告诉它。我最近开始了一份新工作，以前在 MSSQL 上使用子查询不是问题，对性能几乎没有影响，但我想知道这是否是 Postgres 的问题？

我在我运行的最后一个查询中限制了 10 行，并且花了 3 分钟才恢复 10 行。

代码：

SELECT 
g.id, 
COALESCE(g.description, '') AS description,
(select sum(d.total) from ainvdet d inner join ainvhead h USING (ainvheadid) where d.product_group = g.id and  date_part('year', h.order_date) = date_part('year', current_date)) as salesytd,
(select sum(d.total) from ainvdet d inner join ainvhead h USING (ainvheadid) where d.product_group = g.id and  date_part('year', h.order_date) = date_part('year', current_date)-1) as salesytd1,
(select sum(d.total) from ainvdet d inner join ainvhead h USING (ainvheadid) where d.product_group = g.id and  date_part('year', h.order_date) = date_part('year', current_date)-2) as salesytd2,
(select sum(d.total) from ainvdet d inner join ainvhead h USING (ainvheadid) where d.product_group = g.id and  date_part('year', h.order_date) = date_part('year', current_date)-3) as salesytd3
FROM grpdesc g 
WHERE g.company = 1 AND g.record_type = 'G'
GROUP BY g.id, g.description
postgresql subquery

2 个回答

  1. Best Answer

    如果没有完整的表定义和索引，就无法确定，但这很可能是查询编写不佳的情况。

    这应该返回相同的结果，但要求查询引擎访问行一次，而不是四次（每个子查询一次）。

    SELECT 
  g.id, 
  COALESCE(g.description, '') AS description,
  SUM
    (
      CASE
        WHEN date_part('year', h.order_date) = date_part('year', current_date) THEN d.total
        ELSE 0
      END
    ) AS salesytd,
  SUM
    (
      CASE
        WHEN date_part('year', h.order_date) = date_part('year', current_date) - 1 THEN d.total
        ELSE 0
      END
    ) AS salesytd1,
  SUM
    (
      CASE
        WHEN date_part('year', h.order_date) = date_part('year', current_date) - 2 THEN d.total
        ELSE 0
      END
    ) AS salesytd2,
  SUM
    (
      CASE
        WHEN date_part('year', h.order_date) = date_part('year', current_date) - 3 THEN d.total
        ELSE 0
      END
    ) AS salesytd3
FROM
  grpdesc g
LEFT JOIN
  ainvdet d
    ON d.product_group = g.id
LEFT JOIN 
  ainvhead h 
    ON h.ainvheadid = d.ainvheadid
        AND h.order_date >= date_trunc('YEAR',current_date - INTERVAL '3 YEARS')
WHERE 
  g.company = 1 
    AND g.record_type = 'G'
GROUP BY 
  g.id,
  g.description

    我尽量保持我的答案与 DB 无关，但正如A Horse With No Name指出的那样，代替CASE您可以使用的语句：

    SUM(d.total) filter (where date_part(..) = date_part(...))
    • 4

  2. 你也可以试试：

    SELECT 
  g.id, 
  COALESCE(g.description, '') AS description,
  ' Sales for year: ' || DATE_TRUNC('YEAR', header.order_date)::TEXT || ' ', -- for presentation only - can be deleted!
  SUM(line.total) OVER (PARTITION BY DATE_TRUNC('YEAR', header.order_date))
FROM 
  grpdesc g, 
LEFT JOIN ainvdet  line   ON line.product_group = g.id
LEFT JOIN ainvheadid header ON header.ainvheadid = line.ainvhead
WHERE g.company = 1
  AND g.record_type = 'G'
  AND h.order_date >=  DATE_TRUNC('YEAR', CURRENT_DATE - INTERVAL '3 YEARS');
ORDER BY DATE_TRUNC('YEAR', header.order_date) DESC;

    您必须检查自己系统硬件的性能——我怀疑@bbaird 可能会稍微快一点！就像他的解决方案一样，从到没有 4JOIN秒！headerline

    • 1

相关问题