logo标签列表

PostgreSQL - 查询优化

sqlpostgresqlperformancequery-optimization
3
我有以下查询，需要花费约15-20秒才能运行。 
with cte0 as (
    SELECT
        label,
        date,
        CASE
            WHEN
                Lead(label || date || "number") OVER (PARTITION BY label || date || "number" ORDER BY "label", "date", "number", "time") IS NULL
            THEN
                '1'::numeric
            ELSE
                '0'::numeric
        END As "unique"
    FROM table_data
    LEFT JOIN table_mapper ON
        table_mapper."type" = table_data."type"
    WHERE Date BETWEEN date_trunc('month', current_date - 1) and current_date - 1
)
SELECT 'MTD' as "label", round(sum("unique") / count("unique") *100,1) as "value" FROM cte0 WHERE "date" BETWEEN date_trunc('month', current_date - 1) AND current_date -1
UNION ALL
SELECT 'Week' as "label", round(sum("unique") / count("unique") *100,1) as "value" FROM cte0 WHERE "date" BETWEEN date_trunc('week', current_date - 1) AND current_date -1
UNION ALL
SELECT 'FTD' as "label", round(sum("unique") / count("unique") *100,1) as "value" FROM cte0 WHERE "date" = current_date -1
在表格table_data中，我将在date列上创建索引。 
CREATE INDEX ix_cli_date
  ON table_data
  USING btree
  (date);

表定义 (\d table_data)

Table "public.table_data"
      Column      |          Type          | Modifiers
------------------+------------------------+-----------
 date             | date                   | not null
 number           | bigint                 | not null
 time             | time without time zone | not null
 end time         | time without time zone | not null
 duration         | integer                | not null
 time1            | integer                | not null
 time2            | integer                | not null
 time3            | integer                | not null
 time4            | integer                | not null
 time5            | integer                | not null
 time6            | integer                | not null
 time7            | integer                | not null
 type             | text                   | not null
 name             | text                   | not null
 id1              | integer                | not null
 id2              | integer                | not null
 key              | integer                | not null
 status           | text                   | not null
Indexes:
    "ix_cli_date" btree (date)

表定义 (\d table_mapper)

 Table "public.table_mapper"
   Column   | Type | Modifiers
------------+------+-----------
 type       | text | not null
 label     | text | not null
 label2     | text | not null
 label3     | text | not null
 label4     | text | not null
 label5     | text | not null

查询的EXPLAIN ANALYZE解释

结果 (cost=184342.66..230332.86 rows=3 width=64) (实际时间=23377.923..25695.478 行数=3 循环次数=1)"
  CTE cte0"
    -> 窗口聚合 (cost=121516.06..156751.65 rows=612793 width=23) (实际时间=14578.000..18985.958 行数=696157 循环次数=1)"
          -> 排序 (cost=121516.06..123048.04 rows=612793 width=23) (实际时间=14577.975..17084.405 行数=696157 循环次数=1)"
                排序关键字: (((table_mapper.label || (table_data.date)::text) || (table_data."number")::text)), table_mapper.label, table_data.date, table_data."number", table_data."time""
                排序方法: 外部归并  磁盘: 39480kB"
                -> 哈希左连接 (cost=11.96..37474.21 rows=612793 width=23) (实际时间=1.449..3308.718 行数=696157 循环次数=1)"
                      哈希条件: (table_data."type" = table_mapper."type")"
                      -> 使用ix_cli_date索引扫描的table_data (cost=0.02..29036.36 rows=612793 width=38) (实际时间=0.141..946.648 行数=696157 循环次数=1)"
                            索引条件: ((date >= date_trunc('month'::text, ((('now'::text)::date - 1))::timestamp with time zone)) AND (date   哈希 (cost=7.53..7.53 rows=353 width=25) (实际时间=1.275..1.275 行数=336 循环次数=1)"
                            桶: 1024  批处理: 1  内存使用: 15kB"
                            -> 顺序扫描table_mapper (cost=0.00..7.53 rows=353 width=25) (实际时间=0.020..0.589 行数=336 循环次数=1)"
  -> 追加 (cost=27591.00..73581.21 rows=3 width=64) (实际时间=23377.920..25695.467 行数=3 循环次数=1)"
        -> 聚合 (cost=27591.00..27591.02 rows=1 width=32) (实际时间=23377.917..23377.918 行数=1 循环次数=1)"
              -> CTE cte0的扫描 (cost=0.00..27575.68 rows=3064 width=32) (实际时间=14578.052..22335.236 行数=696157 循环次数=1)"
                    过滤条件: ((date = date_trunc('month'::text, ((('now'::text)::date - 1))::timestamp with time zone)))"
        -> 聚合 (cost=27591.00..27591.02 rows=1 width=32) (实际时间=1741.509..1741.510 行数=1 循环次数=1)"
              -> CTE cte0的扫描 (cost=0.00..27575.68 rows=3064 width=32) (实际时间=20.009..1522.352 行数=168261 循环次数=1)"
                    过滤条件: ((date = date_trunc('week'::text, ((('now'::text)::date - 1))::timestamp with time zone)))"
        -> 聚合 (cost=18399.11..18399.13 rows=1 width=32) (实际时间=576.029..576.030 行数=1 循环次数=1)"
              -> CTE cte0的


描述：



我正在从table_data中获取唯一计数和重复计数，这就是LEAD帮助我的地方，我给列的最后一个重复值赋值0。



假设在一列中有3个x。我为前两个x分别赋值1，第三个x赋值为0。



实际上，通过一个cte，我正在从表table_data中取出整行，并使用lead进行一些计算，在定义的日期范围内将每行的1和0值根据标准进行定义并连接字符串。



如果lead为空，则将其视为1，否则为0。



然后，我分别返回3行MTD、Current Week和FTD，并对从lead得到的sum()和整个行的count(*)进行计算。



对于MTD，我有本月的总和和计数。



对于Week-它是当前周，而FTD是昨天。
1
psql 是 PostgreSQL 的默认命令行界面。每个表都应该有一个 primary key。此外，最好不要使用 reserved words 作为标识符。 - Erwin Brandstetter
1
很好。缺少的是描述。请添加一些解释，说明查询的目的是什么。 - Erwin Brandstetter
1
排序方法：外部合并 磁盘：39480kB => 确保您的 work_mem 值高于此值（在 postgresql.conf 中定义） - FuzzyTree
1
@ErwinBrandstetter - 我已经更新了。当然，work_mem的值已经超过了。由于我没有为表定义任何主键，是否有什么问题会导致表的性能不佳？ - Unknown User
1
您正在使用两个表，table_mapper的定义丢失了。此外，可能应该定义一个外键。您的描述并没有详细说明查询的目的。排序顺序是如何定义的？为什么要关心前一行？ - Erwin Brandstetter
显示剩余14条评论
2个回答
2
WITH cte AS (
   SELECT d.thedate
        , lead(m.label) OVER (PARTITION BY m.label, d.thedate, d.number
                              ORDER BY d.thetime) AS leader
   FROM   table_data d
   LEFT   JOIN table_mapper m USING (type)
   WHERE  thedate BETWEEN date_trunc('month', current_date - 1)
                  AND current_date - 1
   )

SELECT 'MTD' AS label, round(count(leader)::numeric / count(*) * 100, 1) AS val
FROM   cte

UNION ALL
SELECT 'Week', round(count(leader)::numeric / count(*) * 100, 1)
FROM   cte
WHERE  thedate BETWEEN date_trunc('week', current_date - 1) AND current_date - 1

UNION ALL
SELECT 'FTD', round(count(leader)::numeric / count(*) * 100, 1)
FROM   cte
WHERE  thedate = current_date - 1;

CTE对于大表格来说很有意义，这样您只需要扫描一次。对于较小的表格，如果没有使用它可能会更快...

在标准SQL中使用thedate代替保留字date。thetime，uni代替time，unique等。

简化了lead()调用。对于领先的行，您会得到一个值或NULL。这似乎是唯一相关的信息。
在窗口函数的PARTITION子句中重复列在ORDER BY子句中是毫无意义的浪费。

建立在此基础上，count(leader) / count(*)代替sum(uni) / count(uni)会更快一些。count(column)只计算非空值，而count(*)计算所有行。

UNION查询的第一个条件是多余的。

关于数据定义的更多建议和链接，请参见问题的评论。

表设计 / 索引

您应该拥有主键。我建议在 table_data 表中使用 serial 或 IDENTITY 列作为代理主键：

ALTER TABLE table_data ADD COLUMN table_data_id serial PRIMARY KEY;

参见： 将type设置为table_mapper的主键（以下外键约束需要）：
ALTER TABLE table_mapper ADD CONSTRAINT table_mapper_pkey (type);

添加一个外键约束来强制执行引用完整性，例如：type。
ALTER TABLE table_data ADD CONSTRAINT table_data_type_fkey
  FOREIGN KEY (type) REFERENCES table_mapper (type)
  ON UPDATE CASCADE ON DELETE NO ACTION;

为了获得最佳的读取性能（牺牲一些写入性能），可以添加一个多列索引，可能允许上述查询的仅索引扫描。
CREATE INDEX table_data_foo_idx ON table_data (thedate, number, thetime);
1
我需要将“date”列更改为“todate”，还是可以像“date”一样简单地使用“thedate”？这确实将时间缩短到了12秒。 - Unknown User
1
我建议使用任何一个不是保留字或基本类型名称的列名来重命名列：ALTER TABLE table_data RENAME "date" TO my_new_column_name;。详见手册。 - Erwin Brandstetter
1
非常感谢。我会去做的。 - Unknown User
1

根据您的查询，您三次引用了CTE。相反，如果您愿意将值放在三列而不是三行中，则可以使用条件聚合：

SELECT round(sum("date" BETWEEN date_trunc('month', current_date - 1) AND current_date -1 then "unique" else 0 END)) /
             sum("date" BETWEEN date_trunc('month', current_date - 1) AND current_date -1 then 1 else 0 END)) *100,1) as mtd
     . . .
FROM CTE

这可能会加快查询速度。此外，您可以将此逻辑纳入CTE查询本身中，从而消除材料化步骤。
我按照你给我的方法尝试了一下。但是当我运行查询时，出现了“then”附近的语法错误。 - Unknown User
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接