当前位置：首页 > news >正文

网站建设专业知识应用百度企业网盘

news 2025/7/16 21:19:06

网站建设专业知识应用,百度企业网盘,镇江网站建设制作,福田商城网站制作可以使用EXPLAIN的ANALYZE选项获取时间的执行计划预估。使用该选项，EXPLAIN会实际的执行查询，然后返回实际的返回行及运行时间。例如： EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 10 AND t1.unique2 t2.uniq…

可以使用EXPLAIN的ANALYZE选项获取时间的执行计划预估。使用该选项，EXPLAIN会实际的执行查询，然后返回实际的返回行及运行时间。例如：

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=4.65..118.62 rows=10 width=488) (actual time=0.128..0.377 rows=10 loops=1)-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244) (actual time=0.057..0.121 rows=10 loops=1)Recheck Cond: (unique1 < 10)-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.024..0.024 rows=10 loops=1)Index Cond: (unique1 < 10)-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.91 rows=1 width=244) (actual time=0.021..0.022 rows=1 loops=10)Index Cond: (unique2 = t1.unique2)
Planning time: 0.181 ms
Execution time: 0.501 ms

请注意，执行时间与成本（cost）的单位是不同的。

在有些执行计划中，某个子计划可能会执行多次。loops会标出执行的次数，实际时间和返回行未每次执行的平均值。

有些情况下，EXLPAIN ANALYZE会展示更多信息。例如，sort和hash节点会提供额外信息：

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY
t1.fivethous;
QUERY PLAN
-------------------------------------------------------------------------
Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)Sort Key: t1.fivethousSort Method: quicksort Memory: 77kB-> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)Hash Cond: (t2.unique2 = t1.unique2)-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)-> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)Buckets: 1024 Batches: 1 Memory Usage: 28kB-> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)Recheck Cond: (unique1 < 100)-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)Index Cond: (unique1 < 100)
Planning time: 0.194 ms
Execution time: 8.008 ms

也可以展示被条件剔除掉的行数：

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7000 width=244) (actual time=0.016..5.107 rows=7000 loops=1)Filter: (ten < 7)Rows Removed by Filter: 3000
Planning time: 0.083 ms
Execution time: 5.905 ms

还可以使用EXPLAIN的BUFFER选项来获得更多运行时状态：

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100
AND unique2 > 9000;
QUERY PLAN
--------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=25.08..60.21 rows=10 width=244) (actual time=0.323..0.342 rows=10 loops=1)Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))Buffers: shared hit=15-> BitmapAnd (cost=25.08..25.08 rows=10 width=0) (actual time=0.309..0.309 rows=0 loops=1)Buffers: shared hit=7-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)Index Cond: (unique1 < 100)Buffers: shared hit=2-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.227..0.227 rows=999 loops=1)Index Cond: (unique2 > 9000)Buffers: shared hit=5
Planning time: 0.088 ms
Execution time: 0.423 ms

谨记EXPLAIN ANALYZE会实际执行命令，所以对于DML命令，可以考虑将其放置到事务中，并在执行后回滚，以免对实际数据产生影响：

BEGIN;
EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE
unique1 < 100;
QUERY PLAN
---------------------------------------------------------------------------------------------
Update on tenk1 (cost=5.07..229.46 rows=101 width=250) (actual time=14.628..14.628 rows=0 loops=1)-> Bitmap Heap Scan on tenk1 (cost=5.07..229.46 rows=101 width=250) (actual time=0.101..0.439 rows=100 loops=1)Recheck Cond: (unique1 < 100)-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)Index Cond: (unique1 < 100)
Planning time: 0.079 ms
Execution time: 14.727 ms
ROLLBACK;

如果UPDATE或DELETE命令影响到继承关系表，那么执行计划：

EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101;
QUERY PLAN
-----------------------------------------------------------------------------------
Update on parent (cost=0.00..24.53 rows=4 width=14)Update on parentUpdate on child1Update on child2Update on child3-> Seq Scan on parent (cost=0.00..0.00 rows=1 width=14)Filter: (f1 = 101)-> Index Scan using child1_f1_key on child1 (cost=0.15..8.17 rows=1 width=14)Index Cond: (f1 = 101)-> Index Scan using child2_f1_key on child2 (cost=0.15..8.17 rows=1 width=14)Index Cond: (f1 = 101)-> Index Scan using child3_f1_key on child3 (cost=0.15..8.17 rows=1 width=14)Index Cond: (f1 = 101)

查看全文

http://www.lbrq.cn/news/2347507.html