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 导数的方式有点问题。