postgresql-sql语句调优

查看某表已建有的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