正文 

当前位置： 首页 > 问答 >

理解/优化 Postgresql 中的 SQL 查询

     2023-04-14     146

关键词：

【中文标题】理解/优化 Postgresql 中的 SQL 查询【英文标题】：Understand/Optimize SQL query in Postgresql 【发布时间】：2019-12-04 19:09:43 【问题描述】：

所以我有一个查询，我在最坏的情况下运行它，它需要 10-12 分钟。 如果我删除 where 查询的时间检查，它会下降到 20-30 秒，所以我想知道如何优化它？

我尝试在时间戳到时间的转换上添加索引，但它并没有真正帮助...... rs 表（register_status）有超过 7000 万行，register_date 大约 280k，cp 一个不到 1k。

查询的想法是获取在一段时间内按状态分组的 CP 的所有结果，包括在时间范围内。这是最坏的情况，所以这是数据库中的第一个日期，如果用户选择一整天作为时间范围。查询如下：

explain analyze SELECT
    COUNT(rs.status) filter (where rs.status = 'Occ') as total_occ,
    COUNT(rs.status) filter (where rs.status = 'Part') as total_part,
    COUNT(rs.status) filter (where rs.status = 'OOS') as total_oos,
    COUNT(rs.status) filter (where rs.status = 'OOC') as total_ooc,
    cp.id as charge_point_id,
    cp.address,
    cp.type as charge_point_type,
    cp.latitude,
    cp.longitude 
FROM register_date rd 
    inner join register_status rs on rs.fk_register_date = rd.id
    inner join charge_point cp on cp.id = rs.fk_charge_point
WHERE 
 rd.date::date >= '2016-11-01' and rd.date::date <= '2019-08-01'
 AND
 rd.date::time >= time '00:00' AND rd.date::time <= time '23:59'
group by cp.id

而EXPLAIN ANALYZE的结果是下面这个，我可以看到很多空间使用...

"Finalize GroupAggregate  (cost=34412.78..34536.10 rows=780 width=124) (actual time=689440.380..699740.172 rows=813 loops=1)"
"  Group Key: cp.id"
"  ->  Gather Merge  (cost=34412.78..34519.27 rows=722 width=124) (actual time=689421.445..699736.996 rows=1579 loops=1)"
"        Workers Planned: 1"
"        Workers Launched: 1"
"        ->  Partial GroupAggregate  (cost=33412.77..33438.04 rows=722 width=124) (actual time=649515.576..659674.461 rows=790 loops=2)"
"              Group Key: cp.id"
"              ->  Sort  (cost=33412.77..33414.57 rows=722 width=96) (actual time=649496.720..654001.697 rows=24509314 loops=2)"
"                    Sort Key: cp.id"
"                    Sort Method: external merge  Disk: 2649104kB"
"                    Worker 0:  Sort Method: external merge  Disk: 2652840kB"
"                    ->  Nested Loop  (cost=0.56..33378.49 rows=722 width=96) (actual time=1.343..504948.423 rows=24509314 loops=2)"
"                          ->  Parallel Seq Scan on register_date rd  (cost=0.00..6443.69 rows=4 width=4) (actual time=0.021..294.724 rows=139760 loops=2)"
"                                Filter: (((date)::date >= '2016-11-01'::date) AND ((date)::date <= '2019-08-01'::date) AND ((date)::time without time zone >= '00:00:00'::time without time zone) AND ((date)::time without time zone <= '23:59:00'::time without time zone))"
"                          ->  Nested Loop  (cost=0.56..6725.90 rows=780 width=100) (actual time=0.077..3.574 rows=175 loops=279519)"
"                                ->  Seq Scan on charge_point cp  (cost=0.00..21.80 rows=780 width=92) (actual time=0.002..0.077 rows=813 loops=279519)"
"                                ->  Index Only Scan using register_status_fk_charge_point_fk_register_date_status_key on register_status rs  (cost=0.56..8.58 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=227248947)"
"                                      Index Cond: ((fk_charge_point = cp.id) AND (fk_register_date = rd.id))"
"                                      Heap Fetches: 49018627"
"Planning Time: 0.506 ms"
"Execution Time: 700065.010 ms"

【问题讨论】：

所以您有一个名为 date 的 timestamp 列。 【参考方案1】：

横向连接可能会更快：

SELECT cp.*, rd.*
FROM charge_point cp CROSS JOIN LATERAL
     (SELECT COUNT(*) filter (where rs.status = 'Occ') as total_occ,
             COUNT(*) filter (where rs.status = 'Part') as total_part,
             COUNT(*) filter (where rs.status = 'OOS') as total_oos,
             COUNT(*) filter (where rs.status = 'OOC') as total_ooc,
      FROM register_date rd JOIN
           register_status rs 
           ON rs.fk_register_date = rd.id
      WHERE cp.id = rs.fk_charge_point AND
            rd.date >= '2016-11-01' and
            rd.date < '2019-08-01' + interval '1 day'
     ) rd;

建议使用register_date(fk_charge_point, date) 和register_status(id, status) 上的索引。

请注意，我更改了日期比较，因此它们对索引更友好。我认为没有理由按 time 过滤，所以我删除了这些条件。

【讨论】：

注意：) cp; [加：我认为您在此处需要 LATERAL，只有不拆分时间戳就足够了，IMO] 我确实需要时间，因为它可能只需要日期时间在早上 5 点到下午 6 点之间的结果，所以正如我所说，我在最坏的情况下运行查询场景，如果用户选择从 00:00 到 23:59 ，这没有意义，但仍然可能。 @user3107720 。 . .然后重新添加条件。横向连接仍应使用计费点和日期的索引。【参考方案2】：

我使用 Gordon 的方法开发了一个新查询，结果速度更快，从 10-12 分钟到 20-40 秒：

SELECT cp.*, rd.* from charge_point cp cross join lateral
    (select
        COUNT(rs.status) filter (where rs.status = 'Occ') as total_occ,
        COUNT(rs.status) filter (where rs.status = 'Part') as total_part,
        COUNT(rs.status) filter (where rs.status = 'OOS') as total_oos,
        COUNT(rs.status) filter (where rs.status = 'OOC') as total_ooc,
        rs.fk_charge_point as cpid
    FROM register_date rd 
        inner join register_status rs on rs.fk_register_date = rd.id
    WHERE 
        rd.date::date >= '2019-02-01' and rd.date::date <= '2019-08-01'
        AND
        rd.date::time >= time '00:00' AND rd.date::time <= time '23:59'
    group by rs.fk_charge_point) rd
where cp.id = rd.cpid

我仍然需要检查添加任何索引是否会使其更快，但目前看起来不错

【讨论】：

掌握查询利器深入理解postgresql索引原理与优化(代码片段)

文章目录一、前言1.1PostgreSQL索引的重要性1.2本文的结构和目的二、索引概述2.1什么是索引2.2为什么要使用索引2.3索引如何工作三、索引类型3.1B-tree索引3.2Hash索引3.3GiST索引3.4GIN索引四、索引实践4.1创建索引4.2索引的使用4.3索引维... 查看详情

postgresql之慢sql语句(代码片段)

...住慢SQL语句，以对其进行追踪优化。二、数据库参数PostgreSQL中的慢SQL追踪是通过以记录日志的方式进行分析，追踪的，因此，需要优化SQL需要启动日志收集功能。以RPM方式安装的数据库日志收集功能默认是打开的... 查看详情

PostgreSQL 中的优化查询

】PostgreSQL中的优化查询【英文标题】：OptimizedqueryinginPostgreSQL【发布时间】：2015-04-0213:19:36【问题描述】：假设您有一个名为tracker的表，其中包含以下记录。issue_id|ingest_date|verb,status102015-01-2400:00:001,1102015-01-2500:00:002,2102015-01-2600... 查看详情

了解 Postgresql 中的 EXPLAIN 函数

】了解Postgresql中的EXPLAIN函数【英文标题】：UnderstandingEXPLAINfunctioninPostgresql【发布时间】：2015-10-1016:49:20【问题描述】：试图理解EXPLAIN函数-我有两个查询-第一个查询已优化，运行600毫秒（我有100k行），第二个查询运行900毫秒... 查看详情

postgresql之慢sql语句(代码片段)

作者：宋少华一、导读优化在硬件和非系统故障的情况下，对于提升数据库本身的性能非常重要。每一种类型的数据库都有自己不同的方式去跟踪优化数据库，这些方式中不仅仅包含了数据库系统本身参数层面的优化... 查看详情

从 EF 为 PostGresql 生成的查询中的非最佳计数

】从EF为PostGresql生成的查询中的非最佳计数【英文标题】：Non-optimalcountinquerygeneratedfromEFforPostGresql【发布时间】：2017-09-0709:40:40【问题描述】：我在尝试优化我的ef查询时遇到问题。我不知道即使在生成的sql中我可以改变什么，... 查看详情

将 PostgreSql 时间戳查询转换为 MS SQL/Azure SQL

】将PostgreSql时间戳查询转换为MSSQL/AzureSQL【英文标题】：ConvertingPostgreSqltimestampquerytoMSSQL/AzureSQL【发布时间】：2021-12-0513:22:57【问题描述】：我目前正在将一些基本的sql脚本从Postgresql转换为AzureSql。我是sql的新手，但我真的无法... 查看详情

postgresql对orexists的优化(代码片段)

在Oracle数据库中，where条件中有orexists会走Filter，这种情况一般都需要改写SQL先创建2个测试表a,bcreatetableaasselect*fromdba_objects;createtablebasselect*froma;下面SQL在Oracle中会产生filter，跑得慢selectcount(*)fromawhereow 查看详情

postgresql分析慢sql

现象突然发现测试环境一条慢sql,就想着分析一下，写写总结。说到优化其实我个人认为是不到不得已还是没有必要的，毕竟除非特别重大的问题，影响了基本操作和体验，平时还是基本的配置也够了，就像《重构：改善代码的... 查看详情

优化以下 postgreSQL 代码的可能方法是啥？

】优化以下postgreSQL代码的可能方法是啥？【英文标题】：WhatarethepossiblewaystooptimizethebelowpostgreSQLcode?优化以下postgreSQL代码的可能方法是什么？【发布时间】：2019-05-2213:54:52【问题描述】：我编写了这个SQL查询来从greenplumdatalake中... 查看详情

是否可以替换 SQL 中的文字并为 PostgreSQL 中的 SQL EXPLAIN 计划产生相同的结果

】是否可以替换SQL中的文字并为PostgreSQL中的SQLEXPLAIN计划产生相同的结果【英文标题】：CanliteralsinanSQLbesubstitutedandresultinsameoutcomeforaSQLEXPLAINplaninPostgreSQL【发布时间】：2014-02-2123:36:31【问题描述】：假设我有一些任意的SQL，我希... 查看详情

sql计算postgresql中的百分位数(代码片段)

查看详情

sql忽略postgresql中的重复插入(代码片段)

查看详情

优化 PostgreSQL 中的 JOIN -> GROUP BY 查询：所有索引都已经存在

】优化PostgreSQL中的JOIN->GROUPBY查询：所有索引都已经存在【英文标题】：OptimizeJOIN->GROUPBYqueryinPostgreSQL:allindexesarealreadythere【发布时间】：2020-03-2010:49:35【问题描述】：关于SO至少有几个类似（但不完全相同）的问题。在这些... 查看详情

不理解影响 postgresql max_connections 的文档中的描述

】不理解影响postgresqlmax_connections的文档中的描述【英文标题】：Didnotunderstandthedescriptioninthedocumentationthataffectspostgresqlmax_connections【发布时间】：2021-02-2109:00:32【问题描述】：postgresql文档对于max_connections意味着什么-“定义与数据... 查看详情

PostgreSQL 中连接名和姓的搜索优化

】PostgreSQL中连接名和姓的搜索优化【英文标题】：OptimizationofsearchonconcatenatedfirstnameandlastnameinPostgreSQL【发布时间】：2015-08-0507:28:02【问题描述】：我在Postgres中编写了一个SQL查询，它通过名字和姓氏来搜索用户。我的问题只是它... 查看详情

postgresql存储过程(代码片段)

...录一、引言1.1存储过程的定义和作用1.1.1定义1.1.2作用1.2PostgreSQL存储过程的优势和局限性二、PostgreSQL基础2.1PostgreSQL及其历史2.2PostgreSQL的基本数据类型及其用途2.3PostgreSQL的常见计算和逻辑操作符及其顺序三、创建存储过程3.1创建... 查看详情

postgresql对orexists产生的filter优化一(代码片段)

在Oracle数据库中，where条件中有orexists会走Filter，这种情况一般都需要改写SQL先创建2个测试表a,bcreatetableaasselect*fromdba_objects;createtablebasselect*froma;下面SQL在Oracle中会产生filter，跑得慢selectcount(*)fromawhereow 查看详情