代码编织梦想

查看某表已建有的index

select indexname,indexdef  from pg_indexes  where tablename='ip_assets_assets_ports'

查看某个schema下已建有的index

select indexname,indexdef  from pg_indexes  where schemaname='public'

创建索引的语句

create index ip_assets_assets_ports_3 on ip_assets_assets_ports (ip_online, mid, ip, port)

删除索引语句

drop index ip_assets_assets_ports_3

优化前SQL语句,耗时40s

select x.company,x.surviveIpNums,y.portNum,z.softNum,g.hardNum  from 

(select count(distinct ia.search_ip) as surviveIpNums ,ia.mid_name as company,ia.mid from ip_assets_assets ia where ia.ip_online='1' group by mid_name,mid) x
left join 
(select count(distinct(iap.ip,iap.port))  as portNum,iap.mid from ip_assets_assets_ports iap  where iap.ip_online='1' group by iap.mid) y on x.mid=y.mid
left join
(select count(distinct ar.rule_id) as softNum,ar.mid from  ip_assets_assets_rule_infos ar where ar.soft_hard_code=2 and ar.ip_online='1' group by mid)z on y.mid=z.mid 
left join
(select count(distinct ar.rule_id) as hardNum,ar.mid from  ip_assets_assets_rule_infos ar where ar.soft_hard_code=1 and ar.ip_online='1' group by mid) g on z.mid=g.mid

优化后SQL语句,耗时12.5s

SELECT
	x.company,
	x.surviveIpNums,
	y.portNum,
	z.softNum,
	g.hardNum 
FROM
	(
	SELECT COUNT (*) AS surviveIpNums,mid_name AS company,mid FROM 	(SELECT DISTINCT search_ip, mid_name, mid FROM ip_assets_assets WHERE ip_online = '1' ) temp_assets 
	GROUP BY 	mid_name,	mid 
	) x
	LEFT JOIN (
	SELECT COUNT (*) AS portNum,mid FROM (SELECT DISTINCT ip, port, mid FROM ip_assets_assets_ports WHERE ip_online = '1' ) temp_ports GROUP BY 	mid 
	) y ON x.mid = y.mid
	LEFT JOIN ( 
	SELECT COUNT(*) AS softNum,mid FROM (SELECT DISTINCT rule_id,mid FROM ip_assets_assets_rule_infos WHERE soft_hard_code = 2 AND ip_online = '1')  temp_soft GROUP BY mid
  ) z ON y.mid = z.mid
	LEFT JOIN (
	SELECT COUNT(*) AS hardNum,mid FROM (SELECT DISTINCT rule_id,mid FROM ip_assets_assets_rule_infos WHERE soft_hard_code = 1 AND ip_online = '1')  temp_hard GROUP BY mid
	)  g ON z.mid = g.mid

执行计划命令

explain analyse sql
执行结果如下图所示:
在这里插入图片描述

具体调优参数可参考以下链接

https://blog.csdn.net/ls3648098/article/details/7602136

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_44803066/article/details/129421742

greenplum优化--sql调优篇-爱代码爱编程

目录 目录数据库查询预准备 VACUUMANALYZEEXPLAIN执行计划两种聚合方式关联重分布 查询优化 explain参数选择合适分布键分区表压缩表分组扩展窗口函数

postgresql 语句调优-爱代码爱编程

PostgreSQL 语句调优-Sql语句优化 作为一名优秀的码农,对于了解Sql如何调优是很有必要的。。简单总结一下, 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉

greenplum-sql调优_hmxz1024的博客-爱代码爱编程_greenplum sql优化

本文转载自:https://blog.csdn.net/u012948976/article/details/52695397 数据库查询准备 VACUUM vacuum只是简单的回收空间且令其可以再次使用,没有请求排

使用EXPLAIN (postgresql) 对sql进行优化-爱代码爱编程

现在sql优化有两种方向,一种是通过增加缓存的方式,比如通过使用redis , mybites 一二级缓存来提高查询效率。 第二种是通过使用explain来分析慢查询的sql语句, 找到该语句的问题从而进行优化。 这里对使用explain来进行优化的方式做一些介绍。 使用方式:  explain +  sql => 执行 =>  查看结果

PostgreSQL学习02-调优必备之查看sql的执行计划(简单查看)-爱代码爱编程

PostgreSQL学习01-常见SQL写法 实际项目开发中,由于我们不知道实际查询的时候数据库里发生了什么事情,数据库软件是怎样扫描表、怎样使用索引的,因此,我们能感知到的就只有sql语句运行的时间,在数据规模不大时,查询是瞬间的,因此,在写sql语句的时候就很少考虑到性能的问题。但是当数据规模增大,如千万、亿的时候,我们运行同样的sql语句时却发现迟迟

PostgreSQL性能调优 postgresql.conf参数调优-爱代码爱编程

PostgreSQL性能调优 postgresql.conf参数调优 调整硬件配置1.存储器1)OLAP(联机分析处理)2)OLTP(联机事务处理)2.内存容量3.CPU速度数据库参数调整1.连接数2.内存相关参数3.其他参数收集系统统计信息1.日志收集2. pg_stat_statements1.EXPLAIN介绍EXPLAIN 引用的数值是:2

PostgreSQL配置参数调优-爱代码爱编程

/CPU 查看CPU型号/ cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c /查看物理CPU个数/ cat /proc/cpuinfo | grep “physical id” | sort -u | wc -l /查看逻辑CPU个数/ cat /proc/cpuinfo | grep “pr

Postgresql HA(PG-POOL)安装-爱代码爱编程

目录 二、安装步骤 2.1 用户配置 2.1.1 配置专属用户 2.2 环境依赖 2.3 源码下载 2.4 源码安装 2.5 初始化 2.5.1 初始化数据库 2.6 服务管理 2.5.1 启/停数据库 2.5.1 刷新配置 三、主备同步 3.1 主库配置  3.2 备库初始化(同步) 3.3 验证 四、高可用(HA) 4

Postgres-XL 调优-爱代码爱编程

Postgresql-xl 节点介绍 转载: (19条消息) Postgresql-xl 结构_有梦为马 随处可栖-CSDN博客_postgresql xl GTM 全局事务控制节点,保证集群数据一致性,与coordinator节点和datanode节点不断通信,是整个集群的核心节点,只存在一个,可以存在一个GTM standby节点,对GT

postgresql SQL 优化 -- 理论与原理-爱代码爱编程

这里写的是一个系列,关于POSTGRESQL SQL 优化的问题,这篇是这个系列的第二篇,第一篇可以在文字的末尾的连接中找到,之前有同学提出,希望有一个历史文字的连接。 这期就进入正题,一个SQL 语句撰写出来是怎么开始工作的,也就是查询的过程 query processing ,这里从几个步骤入手 1  一个SQL 是如何转换成数据库系统可

15.go语言编程之go-sql-driver操作mysql数据库实践_唯一极客知识分享的博客-爱代码爱编程

本章目录: Go语言标准库操作MySQL数据库 1.MySQL驱动下载2.MySQL驱动格式3.MySQL初始化连接4.MySQL的CRUD操作 库表准备单行查询多行查询插入/更新/删除数据综合实践5.MySQL预处理 基础介绍SQL注入示例演示6.MySQL事务处理Go语言标准库操作MySQL数据库 快速了解 MySQL 数据

postgresql之如何进行sql优化?_秦时明月之君临天下的博客-爱代码爱编程

文章目录 一、明确主题二、目标三、如何进行SQL优化?3.1 pg_stat_activity系统内置活动视图视图主要字段wait_event_typestate3.2 pg_stat_statement SQL执行统计视图3.2.1 pg_stat_statements 视图详细说明中文版(简版)3.2.2 常见案例 Top SQL3.3 大表的

postgresql sql语句优化_空~自由的博客-爱代码爱编程

查询正在执行的所有sql SELECT pid, datname, usename, client_addr, application_name, STATE, backend_start, xact_start, xact_stay, query_start, query_stay, REPLACE ( query, chr(

postgresql 不建议使用独立表空间-爱代码爱编程

  有Oracle使用经历的用户都会认为表空间非常重要,但对PostgreSQL中的表空间却知之甚少。本文将解释它们是什么,何时有用以及是否应该使用它们。 什么是表空间 本质上,PostgreSQL中的表空间是一个包含数据文件的目录。这些数据文件是表、序列、索引和物化视图等对象背后的存储空间。在PostgreSQL中,每个这样的对象都有自己的数据

timescaledb 超表-爱代码爱编程

关于超表 超表(hypertable)是具有特殊功能的PostgreSQL表,可以很容易地处理时间序列数据。与它们交互就像与普通PostgreSQL表交互一样,但在幕后,超表会自动按时间将数据划分为块。 在Timescal

九、gp安装笔记-爱代码爱编程

一、配置文件 GP集群数据库服务的配置文件存储在segment目录上,分为master节点和其他的segment节点,master节点配置存储在: $MASTER_DATA_DIRECTORY/postgresql.co

postgres sql性能优化调优_postgresql 性能优化-爱代码爱编程

可以看出数据库每个表有多大,并且排序 --查出所有表(包含索引)并排序 SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty

postgresql_internals-爱代码爱编程

新年的第一篇博客~  一、 Buffer Cache简介 1. 主要用途         调和内存(ns级)与磁盘(ms级)间的速度差异。         pg不仅用自己的buffer cache,也用os cache,所以它使用了“双缓存”,这也是很多文档推荐shared_buffer参数只设为内存25%(通常不超过16G)的原因。 2