postgres sql 调优一例

2012/03/03

Tags: linux postgres sql

前几天发现有个 sql 跑的超慢,第一次拿到 sql 大家简单分析了一下,觉得是写的有问题,里面有对一个大表的查询,数据量大概 800 万,结果还和好几个小表做了 join,而且还是 left join,速度可想而知了。单独对那个大表查询,其实也就是几分钟的事情。



所以建议就是先对小表做 join,然后再和大表做一次 join。不过结果并不理想,时间依然还是那么长。这个时候就得仔细看执行计划了,如下。



能看到虽然人肉对 sql 做了一些优化,但是 sql 并没有按照我们的期望去执行,执行计划里面还是首选去查 fact_tuan_rank_detail 这个大表,速度肯定慢了。


 Nested Loop Left Join  (cost=447.90..1003.43 rows=2 width=620)
   Join Filter: (team.id = team_arrive_city.team_id)
   ->  Nested Loop  (cost=77.62..85.98 rows=1 width=588)
         ->  HashAggregate  (cost=77.62..77.68 rows=1 width=71)
               ->  Index Scan using date_idx on fact_tuan_rank_detail  (cost=0.00..77.60 rows=1 width=71)
                     Index Cond: ((thedate >= '2012-02-25'::date) AND (thedate <= '2012-02-27'::date))
                     Filter: (((source)::text ~~ '%team%'::text) AND ((source)::text !~~ '%today%'::text) AND ((source)::text !~~ '%ongoing%'::text) AND ((s
ource)::text !~~ '%special%'::text))
         ->  Index Scan using team_pkey on team  (cost=0.00..8.28 rows=1 width=32)
               Index Cond: (team.id = fact_tuan_rank_detail.team_id)
               Filter: ((to_timestamp((team.end_time)::double precision))::date > '2012-02-27'::date)
   ->  HashAggregate  (cost=370.29..589.15 rows=14591 width=15)
         ->  Seq Scan on team_arrive_city  (cost=0.00..288.19 rows=16419 width=15)
(12 rows)


仔细研究之后,发现了 rows=1 这个信息。这就是为什么查询分析器先对这个表做查询了,因为他认为这个表最小。



此后对这个表执行了一下 analyse 命令,更新了一些统计信息。然后再看执行计划如下。


 Hash Join  (cost=1210761.12..1326052.45 rows=2282704 width=620)
   Hash Cond: (fact_tuan_rank_detail.team_id = team.id)
   ->  HashAggregate  (cost=1203912.40..1265555.26 rows=1027381 width=71)
         ->  Index Scan using date_idx on fact_tuan_rank_detail  (cost=0.00..1075489.81 rows=10273807 width=71)
               Index Cond: ((thedate >= '2012-02-25'::date) AND (thedate <= '2012-02-27'::date))
               Filter: (((source)::text ~~ '%team%'::text) AND ((source)::text !~~ '%today%'::text) AND ((source)::text !~~ '%ongoing%'::text) AND ((source)
::text !~~ '%special%'::text))
   ->  Hash  (cost=6666.33..6666.33 rows=14591 width=64)
         ->  Merge Left Join  (cost=6414.63..6666.33 rows=14591 width=64)
               Merge Cond: (team.id = b.team_id)
               ->  Sort  (cost=4670.40..4686.82 rows=6567 width=32)
                     Sort Key: team.id
                     ->  Seq Scan on team  (cost=0.00..4254.02 rows=6567 width=32)
                           Filter: ((to_timestamp((end_time)::double precision))::date > '2012-02-27'::date)
               ->  Sort  (cost=1744.23..1780.71 rows=14591 width=40)
                     Sort Key: b.team_id
                     ->  Subquery Scan on b  (cost=370.29..735.06 rows=14591 width=40)
                           ->  HashAggregate  (cost=370.29..589.15 rows=14591 width=15)
                                 ->  Seq Scan on team_arrive_city  (cost=0.00..288.19 rows=16419 width=15)
(18 rows)


可以看到执行计划已经变了,先做其他表的 join,最后再和大表 join。并且提示的执行时间也大致靠谱。



从这里面引申一下,时常会听到有人说 explain 命令执行后得出的执行时间不靠谱,需要使用 explain analyse。可是为什么不靠谱呢,其实 explain analyse 需要的时间和实际执行时间一样,explain 不靠谱的原因是因为数据库对那个表的统计信息不及时导致的。



再进一步了解,postgres 里面这个统计信息为什么不靠谱呢?难道还总是需要我维护这些信息啊?



其实 postgres 里面有个 autovacuum 进程就是做这个事情的。autovacuum 进程默认是启用的。他会在数据库空闲的时候,对数据库做 vavcuum 和 analyse。具体多久执行一次,文档 里面都有写,建议多看看这个页面里面的信息。



此外,还发现 postgres 还提供了很多 数据库状态查询函数 ,使用这里面函数可以查到每个表最后一次 analyse 的时间,vacuum 的时
间,里面索引被使用的情况等等,好多信息。


ps: 使用 analyse 之后,那个 sql 好用了,可是发现过两天又不行了,查看 explain select * from t1 好像没问题,那怎么回事呢?开始没想明白,只好继续 analyse 一下,又好了。可过了两天又不行了。这次得细看了。最后发现是因为真实的 sql 是有 where 条件的,日期条件限定的那部分数据查询分析器认为很少导致了问题。没办法后面只好每次导数都 analyse 一下了。发现 pg_bulkload 导数的方式有点问题。

Comments