PostgreSQL-慢sql查询优化过程-大SQL文件分割-大表分页提交update结果

已于 2024-07-24 18:36:23 修改
阅读量926 收藏 20
点赞数 30
文章标签： 数据库 postgresql java
于 2024-07-24 18:33:29 首次发布
江节胜-胜行天下
本文链接：https://blog.csdn.net/weixin_42933305/article/details/140670438
版权

最新版本更新
https://code.jiangjiesheng.cn/article/345

本文关键词
        大SQL文件分割，大表分页提交update结果、执行计划、主键、索引。

重要说明

        本文涉及到的分析过程主要基于1.7本地环境，最终结果是否适用于线上环境，暂未验证。

1. 背景

标样核查记录异常 Tab

WHERE中的某些特定查询值在navicat中执行非常缓慢（在页面上调用接口今天未重现，返回比较快）

2. 整体说明

考虑到在线上环境操作表结构可能会影响用户使用，所以将线上数据同步到本地1.7数据库环境。

2.1. 同步数据到本地1.7数据库环境

通过navicat导出线上的t_water_md_run_log_main.sql、t_water_md_run_log_sub.sql非常大，分别有1.36G、4.44G。

将t_water_md_run_log_sub.sql本地环境导入到1.7数据库的过程中，非常缓慢，近2个小时，最终实时的处理进度也不太更新，整体无法导入。t_water_md_run_log_main.sql这个文件本身相对小，且导入前对文件做了一下修改保存，此表导入成功。

考虑要将t_water_md_run_log_sub.sql大文件分割成小文件，在网上未找到免费工具，所以通过Java代码来处理分割，相关代码和编码说明见 Java-小工具-大文件Sql分割小文件-navicat导入大sql文件中止现象说明 | 胜行天下网。通过将大文件分割成3个小文件并再次尝试导入，实测导入成功。

2.2. 慢SQL

SELECT COUNT
    ( * ) 
  FROM
    (
      SELECT
      main.monitor_id,
      main.equip_name,
      main.pol_id,
      to_char( main.data_time, 'YYYY-MM-DD' ) AS data_time,
      COUNT ( 1 ) AS warning_count 
      FROM
      t_water_md_run_log_main main
      INNER JOIN t_water_md_run_log_sub s ON main."id" = s.main_id 
      WHERE
      s.info_id = 'i13230' 
      AND s.info = '2' 
      AND main.dt = '105' 
      -- AND main.monitor_id IN ( 11111 ) 
      AND main.data_time BETWEEN '2024-06-01 00:00:00'
      AND '2024-07-01 23:59:59' 
      AND s.data_time BETWEEN '2024-06-01 00:00:00'
      AND '2024-07-01 23:59:59' 
      GROUP BY
      main.monitor_id,
      main.pol_id,
      main.equip_name,
      to_char( main.data_time, 'YYYY-MM-DD' ) 
      HAVING
      COUNT ( 1 ) >= 2 
      ORDER BY
      data_time DESC,
      warning_count DESC,
      main.pol_id,
      main.monitor_id 
    ) TOTAL

在线上环境通过navicat执行上述SQL，需要37秒左右，在本地环境执行，也需要1.6秒左右

2.3. 线上环境执行计划

"Aggregate  (cost=400.17..400.18 rows=1 width=8) (actual time=75638.849..75638.852 rows=1 loops=1)"
"  Output: count(*)"
"  ->  Sort  (cost=400.15..400.16 rows=1 width=83) (actual time=75638.816..75638.829 rows=175 loops=1)"
"        Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text)), (count(1))"
"        Sort Key: (to_char(main.data_time, 'YYYY-MM-DD'::text)) DESC, (count(1)) DESC, main.pol_id, main.monitor_id"
"        Sort Method: quicksort  Memory: 49kB"
"        ->  GroupAggregate  (cost=400.11..400.14 rows=1 width=83) (actual time=75637.021..75637.605 rows=175 loops=1)"
"              Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text)), count(1)"
"              Group Key: main.monitor_id, main.pol_id, main.equip_name, (to_char(main.data_time, 'YYYY-MM-DD'::text))"
"              Filter: (count(1) >= 2)"
"              Rows Removed by Filter: 690"
"              ->  Sort  (cost=400.11..400.11 rows=1 width=75) (actual time=75637.005..75637.090 rows=1133 loops=1)"
"                    Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text))"
"                    Sort Key: main.monitor_id, main.pol_id, main.equip_name, (to_char(main.data_time, 'YYYY-MM-DD'::text)) DESC"
"                    Sort Method: quicksort  Memory: 207kB"
"                    ->  Nested Loop  (cost=0.86..400.10 rows=1 width=75) (actual time=15.619..75628.505 rows=1133 loops=1)"
"                          Output: main.monitor_id, main.equip_name, main.pol_id, to_char(main.data_time, 'YYYY-MM-DD'::text)"
"                          Join Filter: ((main.id)::text = (s.main_id)::text)"
"                          Rows Removed by Join Filter: 23272953"
"                          ->  Index Scan using _t_water_md_run_log_sub_18426_chu_t_water_md_run_log_sub_infoid on _timescaledb_internal._t_water_md_run_log_sub_18426_chunk s  (cost=0.43..147.71 rows=1 width=20) (actual time=1.582..7.072 rows=1133 loops=1)"
"                                Output: s.main_id"
"                                Index Cond: (((s.info_id)::text = 'i13230'::text) AND ((s.info)::text = '2'::text))"
"                                Filter: ((s.data_time >= '2024-06-01 00:00:00'::timestamp without time zone) AND (s.data_time <= '2024-07-01 23:59:59'::timestamp without time zone))"
"                                Rows Removed by Filter: 2303"
"                          ->  Index Scan using _t_water_md_run_log_main_18425__t_water_md_run_log_main_data_ti on _timescaledb_internal._t_water_md_run_log_main_18425_chunk main  (cost=0.43..249.45 rows=235 width=71) (actual time=0.114..63.455 rows=20542 loops=1133)"
"                                Output: main.monitor_id, main.equip_name, main.pol_id, main.data_time, main.id"
"                                Index Cond: ((main.data_time >= '2024-06-01 00:00:00'::timestamp without time zone) AND (main.data_time <= '2024-07-01 23:59:59'::timestamp without time zone) AND ((main.dt)::text = '105'::text))"
"Planning Time: 1.141 ms"
"Execution Time: 75638.902 ms"
"QUERY PLAN"
"Aggregate  (cost=400.15..400.16 rows=1 width=8) (actual time=34283.887..34283.888 rows=1 loops=1)"
"  Output: count(*)"
"  ->  GroupAggregate  (cost=400.11..400.14 rows=1 width=83) (actual time=34283.539..34283.877 rows=175 loops=1)"
"        Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text)), NULL::bigint"
"        Group Key: main.monitor_id, main.pol_id, main.equip_name, (to_char(main.data_time, 'YYYY-MM-DD'::text))"
"        Filter: (count(1) >= 2)"
"        Rows 
 by Filter: 690"
"        ->  Sort  (cost=400.11..400.11 rows=1 width=75) (actual time=34283.528..34283.567 rows=1133 loops=1)"
"              Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text))"
"              Sort Key: main.monitor_id, main.pol_id, main.equip_name, (to_char(main.data_time, 'YYYY-MM-DD'::text))"
"              Sort Method: quicksort  Memory: 207kB"
"              ->  Nested Loop  (cost=0.86..400.10 rows=1 width=75) (actual time=58.503..34280.449 rows=1133 loops=1)"
"                    Output: main.monitor_id, main.equip_name, main.pol_id, to_char(main.data_time, 'YYYY-MM-DD'::text)"
"                    Join Filter: ((main.id)::text = (s.main_id)::text)"
"                    Rows Removed by Join Filter: 23272953"
"                    ->  Index Scan using _t_water_md_run_log_sub_18426_chu_t_water_md_run_log_sub_infoid on _timescaledb_internal._t_water_md_run_log_sub_18426_chunk s  (cost=0.43..147.71 rows=1 width=20) (actual time=2.085..4.981 rows=1133 loops=1)"
"                          Output: s.main_id"
"                          Index Cond: (((s.info_id)::text = 'i13230'::text) AND ((s.info)::text = '2'::text))"
"                          Filter: ((s.data_time >= '2024-06-01 00:00:00'::timestamp without time zone) AND (s.data_time <= '2024-07-01 23:59:59'::timestamp without time zone))"
"                          Rows Removed by Filter: 2303"
"                    ->  Index Scan using _t_water_md_run_log_main_18425__t_water_md_run_log_main_data_ti on _timescaledb_internal._t_water_md_run_log_main_18425_chunk main  (cost=0.43..249.45 rows=235 width=71) (actual time=0.061..28.805 rows=20542 loops=1133)"
"                          Output: main.monitor_id, main.equip_name, main.pol_id, main.data_time, main.id"
"                          Index Cond: ((main.data_time >= '2024-06-01 00:00:00'::timestamp without time zone) AND (main.data_time <= '2024-07-01 23:59:59'::timestamp without time zone) AND ((main.dt)::text = '105'::text))"
"Planning Time: 1.289 ms"
"Execution Time: 34283.931 ms"

2.4. 本地环境执行计划

Aggregate  (cost=283615.39..283615.40 rows=1 width=8) (actual time=1247.733..1349.332 rows=1 loops=1)
  Output: count(*)
  ->  Sort  (cost=283615.37..283615.37 rows=1 width=82) (actual time=1247.686..1349.300 rows=175 loops=1)
        Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text)), (count(1))
        Sort Key: (to_char(main.data_time, 'YYYY-MM-DD'::text)) DESC, (count(1)) DESC, main.pol_id, main.monitor_id
        Sort Method: quicksort  Memory: 49kB
        ->  Finalize GroupAggregate  (cost=283614.92..283615.36 rows=1 width=82) (actual time=1245.217..1348.900 rows=175 loops=1)
              Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text)), count(1)
              Group Key: main.monitor_id, main.pol_id, main.equip_name, (to_char(main.data_time, 'YYYY-MM-DD'::text))
              Filter: (count(1) >= 2)
              Rows Removed by Filter: 691
              ->  Gather Merge  (cost=283614.92..283615.30 rows=3 width=82) (actual time=1245.176..1348.102 rows=1030 loops=1)
                    Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text)), (PARTIAL count(1))
                    Workers Planned: 3
                    Workers Launched: 3
                    ->  Partial GroupAggregate  (cost=282614.88..282614.91 rows=1 width=82) (actual time=1226.984..1227.246 rows=258 loops=4)
                          Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text)), PARTIAL count(1)
                          Group Key: main.monitor_id, main.pol_id, main.equip_name, (to_char(main.data_time, 'YYYY-MM-DD'::text))
                          Worker 0: actual time=1221.558..1221.776 rows=219 loops=1
                          Worker 1: actual time=1221.618..1221.895 rows=266 loops=1
                          Worker 2: actual time=1222.114..1222.380 rows=249 loops=1
                          ->  Sort  (cost=282614.88..282614.88 rows=1 width=74) (actual time=1226.974..1227.001 rows=284 loops=4)
                                Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text))
                                Sort Key: main.monitor_id, main.pol_id, main.equip_name, (to_char(main.data_time, 'YYYY-MM-DD'::text)) DESC
                                Sort Method: quicksort  Memory: 69kB
                                Worker 0:  Sort Method: quicksort  Memory: 58kB
                                Worker 1:  Sort Method: quicksort  Memory: 66kB
                                Worker 2:  Sort Method: quicksort  Memory: 64kB
                                Worker 0: actual time=1221.548..1221.571 rows=237 loops=1
                                Worker 1: actual time=1221.609..1221.636 rows=294 loops=1
                                Worker 2: actual time=1222.105..1222.131 rows=284 loops=1
                                ->  Parallel Hash Join  (cost=253063.76..282614.87 rows=1 width=74) (actual time=1196.893..1226.517 rows=284 loops=4)
                                      Output: main.monitor_id, main.equip_name, main.pol_id, to_char(main.data_time, 'YYYY-MM-DD'::text)
                                      Hash Cond: ((main.id)::text = (s.main_id)::text)
                                      Worker 0: actual time=1191.423..1221.131 rows=237 loops=1
                                      Worker 1: actual time=1191.683..1221.104 rows=294 loops=1
                                      Worker 2: actual time=1192.017..1221.621 rows=284 loops=1
                                      ->  Parallel Index Scan using t_water_md_run_log_main_data_time_idx on public.t_water_md_run_log_main main  (cost=0.43..29527.11 rows=6510 width=70) (actual time=0.335..27.962 rows=5141 loops=4)
                                            Output: main.id, main.monitor_id, main.mn, main.data_time, main.dt, main.pol_id, main.type, main.mn_id, main.receive_time, main.polluter_name, main.monitor_name, main.equip_type_name, main.equip_name, main.data_time_day
                                            Index Cond: ((main.data_time >= '2024-06-01 00:00:00'::timestamp without time zone) AND (main.data_time <= '2024-07-01 23:59:59'::timestamp without time zone) AND ((main.dt)::text = '105'::text))
                                            Worker 0: actual time=0.524..28.258 rows=4536 loops=1
                                            Worker 1: actual time=0.316..28.064 rows=4605 loops=1
                                            Worker 2: actual time=0.389..28.092 rows=4742 loops=1
                                      ->  Parallel Hash  (cost=253063.24..253063.24 rows=7 width=20) (actual time=1195.371..1195.372 rows=284 loops=4)
                                            Output: s.main_id
                                            Buckets: 1024  Batches: 1  Memory Usage: 136kB
                                            Worker 0: actual time=1190.653..1190.654 rows=295 loops=1
                                            Worker 1: actual time=1190.656..1190.657 rows=276 loops=1
                                            Worker 2: actual time=1191.133..1191.133 rows=255 loops=1
                                            ->  Parallel Seq Scan on public.t_water_md_run_log_sub s  (cost=0.00..253063.24 rows=7 width=20) (actual time=959.834..1195.183 rows=284 loops=4)
                                                  Output: s.main_id
                                                  Filter: ((s.data_time >= '2024-06-01 00:00:00'::timestamp without time zone) AND (s.data_time <= '2024-07-01 23:59:59'::timestamp without time zone) AND ((s.info_id)::text = 'i13230'::text) AND ((s.info)::text = '2'::text))
                                                  Rows Removed by Filter: 3917742
                                                  Worker 0: actual time=963.670..1190.456 rows=295 loops=1
                                                  Worker 1: actual time=951.701..1190.465 rows=276 loops=1
                                                  Worker 2: actual time=952.403..1190.954 rows=255 loops=1
Planning Time: 2.714 ms
Execution Time: 1349.576 ms

3. 调试过程

3.1. 尝试对group by 字段建索引

CREATE INDEX "idx_group" ON "public"."t_water_md_run_log_main" USING btree (
  "monitor_id" ,
  "pol_id",
  "equip_name" ,
  "data_time"
);

实测，执行效率没有改善

3.2. 尝试对order by字段建索引

CREATE INDEX "idx_order" ON "public"."t_water_md_run_log_main" (
  "data_time",
  "pol_id",
  "monitor_id"
);

实测，执行效率没有改善

3.3. 新建data_time_day字段保存来自data_time的年月日值

为了排除to_char函数对索引的影响。

ALTER TABLE "public"."t_water_md_run_log_main" 
		  ADD COLUMN "data_time_day" DATE;
UPDATE t_water_md_run_log_main SET data_time_day = data_time::DATE;

遇到问题，由于t_water_md_run_log_main表有474w条数据，上面的UPDATE SQL整体是一个超大事务，经过2个小时也无法完成提交。

尝试通过AI工具生成分页执行的SQL：

WITH batches AS (
  SELECT id
  FROM generate_series(1, (SELECT COUNT(*) FROM t_water_md_run_log_main)/10000) AS batch_num,
     LATERAL (
       SELECT id
       FROM t_water_md_run_log_main
       LIMIT 10000
       OFFSET (batch_num - 1) * 10000
     ) AS subquery(id)
)
UPDATE t_water_md_run_log_main
SET data_time_day = data_time::date
WHERE EXISTS (SELECT 1 FROM batches WHERE batches.id = t_water_md_run_log_main.id);

实测，上面的sql一样查询不动，修改10000为更小的值也查询不动。

感觉还是需要带有分页功能的SQL条提交：

UPDATE t_water_md_run_log_main SET data_time_day = data_time::date WHERE id in (SELECT id from  t_water_md_run_log_main where data_time_day is null limit 50*10000 )

修改具体分页大小测试执行效率：
20w 371秒，10w 181秒，5w 112秒，2.5w 43秒 1.25w 22秒

最终决定采用每次修改10w条。但是每次都需要人工执行，这样非常麻烦，所以需要借助类似for循环的PgSQL语法。通过AI工具生成并调试优化，最终如下：

3.3.1. 分页commit update数据SQL（关键SQL）
-- 30 x 100000 = 300 x 10w = 300w 
DO $$
DECLARE
  i integer;
BEGIN
  FOR i IN 1..30 LOOP
   RAISE NOTICE '执行第: % 次', i;
   UPDATE t_water_md_run_log_main SET data_time_day = data_time::date WHERE id in (SELECT id from  t_water_md_run_log_main where data_time_day is null limit 100000 ) ;
   -- COMMIT非常关键，不然整个for循环就是1个大事务
   -- COMMIT非常关键，不然整个for循环就是1个大事务
   -- COMMIT非常关键，不然整个for循环就是1个大事务
   COMMIT;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

查询时间: 4489.502s

实时查询上述更新的数量（2条SQL均可使用）：

SELECT * FROM
		(
		SELECT  '未修改数量' AS "修改状态", COUNT (1), 1 AS orderNum FROM t_water_md_run_log_main WHERE data_time_day IS NULL UNION
		SELECT '已修改数量' AS "修改状态", COUNT (1), 2 AS orderNum FROM t_water_md_run_log_main WHERE data_time_day IS NOT NULL
		) T ORDER BY orderNum ASC;

SELECT COUNT (data_time_day IS NULL OR NULL) AS "未修改数量",COUNT (data_time_day IS NOT NULL OR NULL) AS "已修改数量" FROM t_water_md_run_log_main;

3.4. 新data_time_day字段不建索引

实测，执行效率没有改善，首次执行需要71秒，第二次也是1.6秒

执行计划如下：

Aggregate  (cost=283615.38..283615.39 rows=1 width=8) (actual time=1227.494..1314.633 rows=1 loops=1)
  Output: count(*)
  ->  Sort  (cost=283615.36..283615.37 rows=1 width=54) (actual time=1227.447..1314.600 rows=175 loops=1)
        Output: main.monitor_id, main.equip_name, main.pol_id, main.data_time_day, (count(1))
        Sort Key: main.data_time_day DESC, (count(1)) DESC, main.pol_id, main.monitor_id
        Sort Method: quicksort  Memory: 49kB
        ->  Finalize GroupAggregate  (cost=283614.91..283615.35 rows=1 width=54) (actual time=1225.343..1314.390 rows=175 loops=1)
              Output: main.monitor_id, main.equip_name, main.pol_id, main.data_time_day, count(1)
              Group Key: main.monitor_id, main.pol_id, main.equip_name, main.data_time_day
              Filter: (count(1) >= 2)
              Rows Removed by Filter: 691
              ->  Gather Merge  (cost=283614.91..283615.29 rows=3 width=54) (actual time=1225.319..1313.577 rows=1045 loops=1)
                    Output: main.monitor_id, main.equip_name, main.pol_id, main.data_time_day, (PARTIAL count(1))
                    Workers Planned: 3
                    Workers Launched: 3
                    ->  Partial GroupAggregate  (cost=282614.87..282614.90 rows=1 width=54) (actual time=1207.692..1207.949 rows=261 loops=4)
                          Output: main.monitor_id, main.equip_name, main.pol_id, main.data_time_day, PARTIAL count(1)
                          Group Key: main.monitor_id, main.pol_id, main.equip_name, main.data_time_day
                          Worker 0: actual time=1202.589..1202.824 rows=232 loops=1
                          Worker 1: actual time=1202.551..1202.780 rows=240 loops=1
                          Worker 2: actual time=1202.774..1203.036 rows=279 loops=1
                          ->  Sort  (cost=282614.87..282614.88 rows=1 width=46) (actual time=1207.681..1207.708 rows=284 loops=4)
                                Output: main.monitor_id, main.equip_name, main.pol_id, main.data_time_day
                                Sort Key: main.monitor_id, main.pol_id, main.equip_name, main.data_time_day DESC
                                Sort Method: quicksort  Memory: 56kB
                                Worker 0:  Sort Method: quicksort  Memory: 49kB
                                Worker 1:  Sort Method: quicksort  Memory: 50kB
                                Worker 2:  Sort Method: quicksort  Memory: 54kB
                                Worker 0: actual time=1202.579..1202.603 rows=250 loops=1
                                Worker 1: actual time=1202.540..1202.565 rows=256 loops=1
                                Worker 2: actual time=1202.764..1202.793 rows=303 loops=1
                                ->  Parallel Hash Join  (cost=253063.76..282614.86 rows=1 width=46) (actual time=1178.562..1207.279 rows=284 loops=4)
                                      Output: main.monitor_id, main.equip_name, main.pol_id, main.data_time_day
                                      Hash Cond: ((main.id)::text = (s.main_id)::text)
                                      Worker 0: actual time=1173.537..1202.192 rows=250 loops=1
                                      Worker 1: actual time=1173.547..1202.144 rows=256 loops=1
                                      Worker 2: actual time=1173.456..1202.300 rows=303 loops=1
                                      ->  Parallel Index Scan using t_water_md_run_log_main_data_time_idx on public.t_water_md_run_log_main main  (cost=0.43..29527.11 rows=6510 width=66) (actual time=0.366..27.647 rows=5141 loops=4)
                                            Output: main.id, main.monitor_id, main.mn, main.data_time, main.dt, main.pol_id, main.type, main.mn_id, main.receive_time, main.polluter_name, main.monitor_name, main.equip_type_name, main.equip_name, main.data_time_day
                                            Index Cond: ((main.data_time >= '2024-06-01 00:00:00'::timestamp without time zone) AND (main.data_time <= '2024-07-01 23:59:59'::timestamp without time zone) AND ((main.dt)::text = '105'::text))
                                            Worker 0: actual time=0.467..27.903 rows=4419 loops=1
                                            Worker 1: actual time=0.427..27.688 rows=4989 loops=1
                                            Worker 2: actual time=0.458..27.753 rows=4653 loops=1
                                      ->  Parallel Hash  (cost=253063.24..253063.24 rows=7 width=20) (actual time=1177.240..1177.241 rows=284 loops=4)
                                            Output: s.main_id
                                            Buckets: 1024  Batches: 1  Memory Usage: 136kB
                                            Worker 0: actual time=1172.644..1172.645 rows=270 loops=1
                                            Worker 1: actual time=1172.639..1172.640 rows=291 loops=1
                                            Worker 2: actual time=1172.829..1172.830 rows=279 loops=1
                                            ->  Parallel Seq Scan on public.t_water_md_run_log_sub s  (cost=0.00..253063.24 rows=7 width=20) (actual time=940.033..1177.052 rows=284 loops=4)
                                                  Output: s.main_id
                                                  Filter: ((s.data_time >= '2024-06-01 00:00:00'::timestamp without time zone) AND (s.data_time <= '2024-07-01 23:59:59'::timestamp without time zone) AND ((s.info_id)::text = 'i13230'::text) AND ((s.info)::text = '2'::text))
                                                  Rows Removed by Filter: 3917742
                                                  Worker 0: actual time=943.010..1172.465 rows=270 loops=1
                                                  Worker 1: actual time=927.739..1172.444 rows=291 loops=1
                                                  Worker 2: actual time=943.202..1172.639 rows=279 loops=1
Planning Time: 2.619 ms
Execution Time: 1314.890 ms

3.5. 尝试对data_time_day新字段建索引

CREATE INDEX "idx_new_field" ON "public"."t_water_md_run_log_main" (
  "monitor_id",
  "equip_name",
  "pol_id",
  "data_time_day"
);

实测，执行效率没有改善

3.6. 关键开始->突破

SELECT
  main.monitor_id,
  main.equip_name,
  main.pol_id,
  main.data_time_day AS data_time
FROM
  t_water_md_run_log_main main 
  INNER JOIN t_water_md_run_log_sub s ON main.id = s.main_id 
WHERE
  s.info_id = 'i13230' 
  AND s.info = '2' 
  AND main.dt = '105' 
  --    AND main.monitor_id IN ( 11111 ) 
  AND main.data_time BETWEEN '2024-06-01 00:00:00'
  AND '2024-07-01 23:59:59' 
  AND s.data_time BETWEEN '2024-06-01 00:00:00'
  AND '2024-07-01 23:59:59'

通过分析代码和表结构，发现缺少join on 条件： and main.monitor_id=s.monitor_id (关键1/3)

在线上环境添加on条件后，执行时间从36秒降低到19秒。

另外发现t_water_md_run_log_main表缺少id主键设置，结合where条件，一次性添加相关的主键和索引：

DROP INDEX "public"."idx_group";
DROP INDEX "public"."idx_new_field";
DROP INDEX "public"."idx_order";

ALTER TABLE "public"."t_water_md_run_log_main"  -- 关键2/3
  ADD PRIMARY KEY ("id");
CREATE INDEX "idx_where" ON "public"."t_water_md_run_log_main" (
  "dt",
  "monitor_id",
  "data_time"
);
-- PgSQL的索引名应是模式内唯一
CREATE INDEX "idx_t_water_md_run_log_sub_where" ON "public"."t_water_md_run_log_sub" USING btree ( -- 关键3/3
  "info_id",
  "info",
  "data_time"
);
CREATE INDEX "idx_t_water_md_run_log_sub_join" ON "public"."t_water_md_run_log_sub" USING hash (
  "main_id"
);
CREATE INDEX "idx_t_water_md_run_log_sub_join_where" ON "public"."t_water_md_run_log_sub" USING btree (
  "main_id",
  "info_id",
  "info",
  "data_time"
);

再次执行SELECT、group by 、执行计划，消耗时间平均在0.5秒

3.6.1. 最终SQL验证
EXPLAIN ANALYZE VERBOSE SELECT COUNT
    ( * ) 
  FROM
    (
    SELECT
      main.monitor_id,
      main.equip_name,
      main.pol_id,
      to_char( main.data_time, 'YYYY-MM-DD' ) AS data_time,
      COUNT ( 1 ) AS warning_count 
    FROM
      t_water_md_run_log_main main
      INNER JOIN t_water_md_run_log_sub s ON main."id" = s.main_id and main.monitor_id=s.monitor_id
    WHERE
      s.info_id = 'i13230' 
      AND s.info = '2' 
      AND main.dt = '105' 
--    AND main.monitor_id IN ( 11111 ) 
      AND main.data_time BETWEEN '2024-06-01 00:00:00'
      AND '2024-07-01 23:59:59' 
      AND s.data_time BETWEEN '2024-06-01 00:00:00'
      AND '2024-07-01 23:59:59' 
    GROUP BY
      main.monitor_id,
      main.pol_id,
      main.equip_name,
      to_char( main.data_time, 'YYYY-MM-DD' ) 
    HAVING
      COUNT ( 1 ) >= 2 
    ORDER BY
      data_time DESC,
      warning_count DESC,
      main.pol_id,
    main.monitor_id 
  ) TOTAL；

   SELECT
      main.monitor_id,
      main.equip_name,
      main.pol_id,
      to_char( main.data_time, 'YYYY-MM-DD' ) AS data_time
    FROM
      t_water_md_run_log_main main
      INNER JOIN t_water_md_run_log_sub s ON main."id" = s.main_id and main.monitor_id=s.monitor_id
    WHERE
      s.info_id = 'i13230' 
      AND s.info = '2' 
      AND main.dt = '105' 
--    AND main.monitor_id IN ( 11111 ) 
      AND main.data_time BETWEEN '2024-06-01 00:00:00'
      AND '2024-07-01 23:59:59' 
      AND s.data_time BETWEEN '2024-06-01 00:00:00'
      AND '2024-07-01 23:59:59';

执行计划：

Aggregate  (cost=133.87..133.88 rows=1 width=8) (actual time=23.879..23.880 rows=1 loops=1)
  Output: count(*)
  ->  Sort  (cost=133.85..133.86 rows=1 width=81) (actual time=23.833..23.850 rows=175 loops=1)
        Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text)), (count(1))
        Sort Key: (to_char(main.data_time, 'YYYY-MM-DD'::text)) DESC, (count(1)) DESC, main.pol_id, main.monitor_id
        Sort Method: quicksort  Memory: 49kB
        ->  GroupAggregate  (cost=133.81..133.84 rows=1 width=81) (actual time=22.532..23.454 rows=175 loops=1)
              Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text)), count(1)
              Group Key: main.monitor_id, main.pol_id, main.equip_name, (to_char(main.data_time, 'YYYY-MM-DD'::text))
              Filter: (count(1) >= 2)
              Rows Removed by Filter: 691
              ->  Sort  (cost=133.81..133.81 rows=1 width=73) (actual time=22.510..22.610 rows=1134 loops=1)
                    Output: main.monitor_id, main.equip_name, main.pol_id, (to_char(main.data_time, 'YYYY-MM-DD'::text))
                    Sort Key: main.monitor_id, main.pol_id, main.equip_name, (to_char(main.data_time, 'YYYY-MM-DD'::text)) DESC
                    Sort Method: quicksort  Memory: 207kB
                    ->  Nested Loop  (cost=0.99..133.80 rows=1 width=73) (actual time=0.122..20.240 rows=1134 loops=1)
                          Output: main.monitor_id, main.equip_name, main.pol_id, to_char(main.data_time, 'YYYY-MM-DD'::text)
                          Inner Unique: true
                          ->  Index Scan using idx_t_water_md_run_log_sub_where on public.t_water_md_run_log_sub s  (cost=0.56..40.34 rows=35 width=24) (actual time=0.054..2.615 rows=1134 loops=1)
                                Output: s.id, s.main_id, s.monitor_id, s.info_id, s.info, s.data_time
                                Index Cond: (((s.info_id)::text = 'i13230'::text) AND ((s.info)::text = '2'::text) AND (s.data_time >= '2024-06-01 00:00:00'::timestamp without time zone) AND (s.data_time <= '2024-07-01 23:59:59'::timestamp without time zone))
                          ->  Index Scan using t_water_md_run_log_main_pkey on public.t_water_md_run_log_main main  (cost=0.43..2.66 rows=1 width=69) (actual time=0.011..0.011 rows=1 loops=1134)
                                Output: main.id, main.monitor_id, main.mn, main.data_time, main.dt, main.pol_id, main.type, main.mn_id, main.receive_time, main.polluter_name, main.monitor_name, main.equip_type_name, main.equip_name, main.data_time_day
                                Index Cond: ((main.id)::text = (s.main_id)::text)
                                Filter: ((main.data_time >= '2024-06-01 00:00:00'::timestamp without time zone) AND (main.data_time <= '2024-07-01 23:59:59'::timestamp without time zone) AND ((main.dt)::text = '105'::text) AND (s.monitor_id = main.monitor_id))
Planning Time: 4.676 ms
Execution Time: 24.017 ms
3.6.2. 结合执行计划，总结最终修改
INNER JOIN t_water_md_run_log_sub s ON main."id" = s.main_id and main.monitor_id=s.monitor_id

ALTER TABLE "public"."t_water_md_run_log_main" 
  ADD PRIMARY KEY ("id");
  
CREATE INDEX "idx_t_water_md_run_log_sub_where" ON "public"."t_water_md_run_log_sub" USING btree (
  "info_id",
  "info",
  "data_time"
);

其他同时添加的索引可能也有作用，这里就不删除验证了。

4. 附录

4.1. AI解读PgSQL中的单引号和双引号

单引号（' '）

单引号主要用于字符串字面量，即文本数据。当你需要在 SQL 查询中插入文本字符串时，你应该使用单引号将字符串包围起来。例如：

SELECT 'Hello, world!';

或者在插入数据时：

INSERT INTO my_table (name) VALUES ('John Doe');

双引号（" "）

双引号则有以下几种用途：

标识符引用：在 SQL 标准中，双引号被用来引用标识符（如表名、列名、函数名等），特别是当这些标识符是关键字或包含空格或特殊字符时。例如：

SELECT "My Column Name" FROM "My Table";

大小写敏感：在 PostgreSQL 中，使用双引号的标识符是大小写敏感的。例如，"MyColumn" 和 "mycolumn" 将被视为两个不同的列名。

保留字作为标识符：如果需要将 SQL 保留字用作标识符（如表名或列名），必须使用双引号。例如：

CREATE TABLE "select" ("from" int);

转义序列：双引号还用于包含转义序列，比如在字符串中插入另一个双引号，需要使用两个双引号。例如：

SELECT '"Hello, ""world""!""';

然而，值得注意的是，在 PL/pgSQL 中编写函数或过程时，字符串常量通常也使用单引号。在定义变量或进行字符串操作时，你同样应该使用单引号。

在编写 SQL 查询或 PL/pgSQL 代码时，确保正确使用单引号和双引号，以避免语法错误和意外的行为。

最新版本更新
https://code.jiangjiesheng.cn/article/345

江节胜-全栈工程师
关注
慢SQL优化总结
程序员野蛮成长
03-19 621
关于in和exists的使用 1.当父查询结果集小于子查询结果集则选择exists，如果父查询结果集大于子查询结果集选择in。（可尝试等价改写） in和exists都有子查询优化，提升子查询，有时候这两的执行计划一样。需要注意的是如果子查询包含了父查询里面的条件，in不会被优化。 2.in里面的值一般不超过100个 3.单表postgresql的in和个数关系不大，都可以走索引。 4.当有连表并且...
【PGCCC】在PostgreSQL中实现高效查询优化：如何利用遗传算法？
最新发布
PGCCC的博客
08-27 732
遗传算法的基本原理遗传算法（Genetic Algorithm, GA）是一种基于生物进化论的优化技术，其核心思想是“适者生存”。它通过模拟自然进化过程来解决复杂的优化问题。遗传算法的工作过程类似于生物界的自然选择、基因遗传和变异过程。编码：将问题的解表示为一个“基因”序列，通常是一个二进制字符串或其他形式的编码。例如，在数据库查询优化中，一个执行计划可以被编码为一个基因序列，表示不同的操作顺序和索引选择等。初始种群生成：创建一个由多个候选解（即基因序列）组成的初始种群。
PostgreSQL 慢 SQL 排查
07-17 786
所谓慢SQL 是指在数据库中执行时间超过指定阈值的语句。慢查询太多，对于业务而言，是有很大风险的，可能随时都会因为某种原因而被触发，并且根据我们的经验，数据库最常出现的问题，都是因为慢查询导致数据库慢了，进而导致整个实例 “雪崩” 从而导致线上故障。本篇文章将介绍 PostgreSQL 慢 SQL 如何定位排查。
PostgreSQL 排查慢 SQL
分享既学习
09-14 3701
所谓慢 SQL是指在数据库中执行时间超过指定阈值的语句。慢查询太多，对于业务而言，是有很大风险的，可能随时都会因为某种原因而被触发，并且根据我们的经验，数据库最常出现的问题，都是因为慢查询导致数据库慢了，进而导致整个实例 “雪崩” 从而导致线上故障。本篇文章将介绍 PostgreSQL 慢 SQL 如何定位排查。
深度剖析PostgreSQL慢SQL：原因与优化方案大揭秘
洛阳泰山的博客
06-27 8193
PostgreSQL是一款强大的关系型数据库，但在实际使用过程中，许多用户经常会遇到慢SQL的问题。这些问题不仅会降低数据库性能，还会直接影响业务流程和用户体验。因此，本文将会深入分析PostgreSQL慢SQL的原因和优化方案，帮助用户更好地利用这个优秀的数据库系统。无论你是初学者还是专业开发者，本文都将为你提供实用的技巧和方法，让你的PostgreSQL数据库始终保持高效快速。
慢SQL优化In PostgreSQL
10-21
1、Create Index Directly 2、Change Conditions to Use Index 3、尽量避免在where子句中对字段进行运算，导致查询规划器放弃使用index 4、尽量避免在where子句中对字段类型进行强制转换，导致查询规划器放弃使用index 5、少用outer join，减少不必要的sub-query层级数【在不影响得到正确结果的前提下】 6、坚决避免select * 和 redundant columns【多余字段】 7、Index on Expressions 8、Partial Indexes 9、Decompose DDL【分解DDL】 10、Comprehensive optimization【综合优化】 11、索引的创建 12、查找需要删除的索引 13、查找重复的索引 14、查找需要维护的索引，并自定创建索引维护SQL 15、一个index size影响query plan的例子
CrackingTheSQLInterview：SQL面试问题汇编-http：//xoraus.github.ioCrackingTheSQLInterview
01-28
- 数据库概念：理解关系型数据库（如MySQL、PostgreSQL、Oracle等）的基本原理，包括数据表、字段、主键、外键等。 - SQL语句类型：SELECT、INSERT、UPDATE、DELETE四大基本操作，以及CREATE、ALTER、DROP用于创建...
Go-Toyorm是Golang的强大的sqlorm库
08-13
9. **结果缓存**：可以开启查询结果缓存，提高重复查询的效率。 ### 三、使用示例 1. 定义结构体： ```go type User struct { Id int `orm:"pk;auto"` Name string `orm:"size(100)"` Email string `orm:...
go-simplesql
01-23
例如，可以定义一个结构体代表数据库表中的记录，然后通过该结构体进行增删查改操作，避免了直接编写SQL语句的繁琐过程。 ```go type User struct { ID int Name string } db := simplesql.New("mysql", "root:...
postgresql slow sql report 慢查询报告
wan_xie2009的专栏
05-31 6085
其实也不完全算PostgreSQL的技巧，应该算是SHELL技巧。 首先要配置好postgresql.conf,让PG记录下慢查询，并且日志固定格式，如下: log_destination = 'csvlog' log_min_duration_statement = 100ms pg_ctl reload -D $PGDATA 来看一个统计的例子: vi digoal.sh
PostgresSQL优化
05-09
PostgresSQL优化
postgresql性能优化
02-24
一个优化的SQL：下面默认以postgresql为例：1.尽量避免2.排序的数据量尽量少，并保证在内存里完成排序。（至于具体什么数据量能在内存中完成排序，不同数据库有不同的配置：oracle是sort_area_size；postgresql是work_mem(integer)，单位是KB，默认值是4MB。mysql是sort_buffer_size注意：该参数对应的分配内存是每连接独占！）1.过滤的数据量比较少，一般来说40%，基本不走索引(会全表扫描)2.保证值的数据类型和字段数据类型要一直。3.对索引的字段进行计算时，必须在
PostgreSQL慢sql原因和优化方案
骐骥筋力成，志在万里外
04-11 8404
PostgreSQL慢sql原因和优化方案
PostgreSQL查询慢sql原因和优化方案
洛阳泰山的博客
08-12 6067
查询慢sql的执行会话，关闭进程。查看数据库后台连接进程查看数据库后台连接进程，但是此条SQL不包含当前查询进程查看当前慢SQL,例如查询执行时间超过1秒的SQL可以使用pg_terminate_backend()终止连接。您必须是超级用户才能使用此功能。这在所有操作系统上都是相同的。
postgresal sql 优化
weixin_42602726的博客
12-29 133
PostgreSQL 是一个开源的关系型数据库管理系统，用于存储和管理数据。它提供了许多工具和特性，可以帮助你优化 SQL 查询的性能。 下面是一些常见的 SQL 优化技巧： 使用索引：通过在表中创建索引，可以加快查询的速度。 使用适当的数据类型：选择合适的数据类型可以减少存储空间，并且可以加快查询的速度。 尽量避免使用 *：避免在 SELECT 语句中使用 *，因为这会导致数据库返回所有字...
PostgreSQL查询优化：让你的SQL查询更高效
竹林幽深
01-29 1436
在数据库应用中，查询是一项频繁的操作。查询优化旨在通过调整数据库查询语句和索引设计，提高查询性能，减少响应时间，从而使系统更加高效运行。对于特定的数据类型和查询需求，可以考虑创建自定义索引。这需要实现相应的操作符和查询支持。-- 示例：在自定义类型上创建GiST索引。
【PostgreSQL】PostgreSQL慢SQL优化思路
tttzzzqqq2018的博客
04-03 623
【代码】【PostgreSQL】PostgreSQL慢SQL优化思路。
postgresql SQL语句优化
liu_xue_xue的专栏
10-13 715
这样的话SELECT ID FROM lb_api_test_task将成为一个子查询，而且不会走索引，每次走一遍全表扫描。SQL优化之一NOT IN 优化。查询正在执行的所有sql。耗时很长的sql语句。
PostgreSQL应用开发者完全指南：SQL高级用法与触发器
"4 应用开发者指南_1.pdf" 是一份关于PostgreSQL数据库的应用开发者指南，涵盖了数据库的基础知识和高级特性，包括SQL语句、数据类型、操作符、对象类型、内置函数、触发器、分区表、异步消息等。 在本指南中，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值