查看查询执行情况
查看查询执行情况#
1 使用\timing命令可以打印出查询执行的时间。
2
3 test=# \timing on
4 Timing is on.
5
6 test=# select * from foo; # 这时再执行 SQL 语句会给出语句执行时间。
7 id | name
8 ----+------
9 1 | hawq
10 2 | hdfs
11 (2 rows)
12
13 Time: 16.369 ms
14
15 test=# \timing off # 关闭时间输出
16 Timing is off.
17
18 使用explain语句可以显示出查询计划。
19 test=# explain select count(*) from foo;
20 QUERY PLAN
21 ------------------------------------------------------------------------------------
22 Aggregate (cost=1.04..1.05 rows=1 width=8)
23 -> Gather Motion 12:1 (slice1; segments: 12) (cost=0.00..1.04 rows=2 width=0)
24 -> Seq Scan on foo (cost=0.00..1.01 rows=1 width=0)
25 Optimizer: Postgres query optimizer
26 NewExecutor: ON
27 (5 rows)
28 test=#
使用 explain analyze 可以显示出查询在具体执行时的状态,包括每一个操作符开始执行时间,以及结束时间,可以帮助用户找到查询的瓶颈,进而优化查询。关于查询计划以及 explain analyze 的执行结果的解释可以参考查询计划与查询执行章节。针对一个查询,可能会有无数个查询计划。得出优化的查询计划是查询优化器的功能。一个查询执行时间的长短与查询的计划有很大关系,所以熟悉查询计划以及具体查询的执行对查询优化有很大意义。
1 test=# explain analyze select count(*) from foo;
2 QUERY PLAN
3 ------------------------------------------------------------------------------------------------------------------------------
4 Aggregate (cost=1.04..1.05 rows=1 width=8) (actual time=6.129..6.132 rows=1 loops=1)
5 1 TupleBatch of 1 Tuple, (1/1/1)(avg/min/max) Tuple/TupleBatch, 47 B
6 -> Gather Motion 12:1 (slice1; segments: 12) (cost=0.00..1.04 rows=2 width=0) (actual time=5.575..5.971 rows=2 loops=1)
7 2 TupleBatch of 2 Tuple, (1/1/1)(avg/min/max) Tuple/TupleBatch, 0 B
8 InputStream Info: 28 byte(s) (76.808 KB/s) in 0.356 ms with 2 read call(s).
9 -> Seq Scan on foo (cost=0.00..1.01 rows=1 width=0) (actual time=1.846..3.567 rows=1 loops=1)
10 (seg4) 1 TupleBatch of 1 Tuple, (1/1/1)(avg/min/max) Tuple/TupleBatch, 0 B
11 (seg4) SkipCtid: on
12 Optimizer: Postgres query optimizer
13 NewExecutor: ON
14 Data Locality Ratio: 1.00
15 Planning Time: 4.371 ms
16 (slice0) Executor memory: 32K bytes.
17 (slice1) Executor memory: 89K bytes avg x 12 workers, 89K bytes max (seg0).
18 Memory used: 262144kB
19 Execution Time: 9.913 ms
20 (18 rows)
21 test=#