行式数据库PostgreSQL 9.04版本评测-技术开发专区

行式数据库PostgreSQL 9.04版本评测

作者：卢涛编辑：董建伟 2011-06-22 00:00 IT168网站原创

　　注意这个命令只是显示SQL语句的执行计划，并不实际执行该语句。另外，explain analyse命令实际执行查询，并显示每个节点实际的查询时间，但由于添加了计时器负载，这种查看计划的方式比正常执行时间会长。

pgdb=# explain analyze select

pgdb-# sum(l_extendedprice) / 7.0 as avg_yearly

pgdb-# from

pgdb-# lineitem,

pgdb-# part

pgdb-# where

pgdb-# p_partkey = l_partkey

pgdb-# and p_brand = 'Brand#23'

pgdb-# and p_container = 'MED CAN'

pgdb-# and l_quantity < (

pgdb(# select

pgdb(# 0.2 * avg(l_quantity)

pgdb(# from

pgdb(# lineitem

pgdb(# where

pgdb(# l_partkey = p_partkey

pgdb(# ) limit 10;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------- Limit (cost=9021151.04..9021151.05 rows=1 width=10) (actual time=4464.833..4464.834 rows=1 loops=1)

-> Aggregate (cost=9021151.04..9021151.05 rows=1 width=10) (actual time=4464.831..4464.831 rows=1 loops=1)

-> Nested Loop (cost=0.00..9021098.49 rows=21020 width=10) (actual time=2.090..4461.326 rows=5387 loops=1)

Join Filter: (public.lineitem.l_quantity < (SubPlan 1))

-> Seq Scan on part (cost=0.00..71607.18 rows=2065 width=4) (actual time=0.290..702.496 rows=2031 loops=1)

Filter: ((p_brand = 'Brand#23'::bpchar) AND (p_container = 'MED CAN'::bpchar))

-> Index Scan using lineitem_fk2 on lineitem (cost=0.00..134.05 rows=31 width=21) (actual time=0.031..0.048 rows=30 loops=2031)

Index Cond: (public.lineitem.l_partkey = part.p_partkey)

SubPlan 1

-> Aggregate (cost=135.45..135.46 rows=1 width=7) (actual time=0.058..0.059 rows=1 loops=61007)

-> Index Scan using lineitem_fk2 on lineitem (cost=0.00..135.37 rows=31 width=7) (actual time=0.004..0.027 rows=31 loops=61007)

Index Cond: (l_partkey = $0)

Total runtime: 4465.003 ms

(13 rows)

Time: 4469.102 ms

　　PostgreSQL提供了一些影响执行计划的开关，比如enable_nestloop，可以启用或禁用nested loop，但这些开关的使用要非常小心，有时候虽然把nested loop改为了其他关联，但导致了全表扫描，那么查询性能非但不能提高，还会下降，比如第17个查询，从估计的执行成本来看，比设置前要大一些，实际执行的结果，比设置前多用了10倍多的时间。

pgdb=# set enable_nestloop=off;

SET

Time: 0.418 ms

pgdb=# explain select

pgdb-# sum(l_extendedprice) / 7.0 as avg_yearly

pgdb-# from

pgdb-# lineitem,

pgdb-# part

pgdb-# where

pgdb-# p_partkey = l_partkey

pgdb-# and p_brand = 'Brand#23'

pgdb-# and p_container = 'MED CAN'

pgdb-# and l_quantity < (

pgdb(# select

pgdb(# 0.2 * avg(l_quantity)

pgdb(# from

pgdb(# lineitem

pgdb(# where

pgdb(# l_partkey = p_partkey

pgdb(# ) limit 10;

QUERY PLAN

----------------------------------------------------------------------------------------------------------

Limit (cost=10372631.10..10372631.12 rows=1 width=10)

-> Aggregate (cost=10372631.10..10372631.12 rows=1 width=10)

-> Hash Join (cost=71632.14..10372580.56 rows=20216 width=10)

Hash Cond: (public.lineitem.l_partkey = part.p_partkey)

Join Filter: (public.lineitem.l_quantity < (SubPlan 1))

-> Seq Scan on lineitem (cost=0.00..1784768.72 rows=59988572 width=21)

-> Hash (cost=71607.32..71607.32 rows=1986 width=4)

-> Seq Scan on part (cost=0.00..71607.32 rows=1986 width=4)

Filter: ((p_brand = 'Brand#23'::bpchar) AND (p_container = 'MED CAN'::bpchar))

SubPlan 1

-> Aggregate (cost=135.45..135.46 rows=1 width=7)

-> Index Scan using lineitem_fk2 on lineitem (cost=0.00..135.37 rows=31 width=7)

Index Cond: (l_partkey = $0)

(13 rows)

Time: 5.140 ms

--实际的执行结果：

QUERY PLAN

-----------------------------------------------------------------------------------------------------

Limit (cost=10372631.10..10372631.12 rows=1 width=10) (actual time=50432.309..50432.311 rows=1 loops=1)

-> Aggregate (cost=10372631.10..10372631.12 rows=1 width=10) (actual time=50432.307..50432.308 rows=1 loops=1)

-> Hash Join (cost=71632.14..10372580.56 rows=20216 width=10) (actual time=767.304..50425.289 rows=5387 loops=1)

Hash Cond: (public.lineitem.l_partkey = part.p_partkey)

Join Filter: (public.lineitem.l_quantity < (SubPlan 1))

-> Seq Scan on lineitem (cost=0.00..1784768.72 rows=59988572 width=21) (actual time=0.047..15139.633 rows=59986052 loops=1)

-> Hash (cost=71607.32..71607.32 rows=1986 width=4) (actual time=761.069..761.069 rows=2031 loops=1)

Buckets: 1024 Batches: 1 Memory Usage: 72kB

-> Seq Scan on part (cost=0.00..71607.32 rows=1986 width=4) (actual time=0.364..758.448 rows=2031 loops=1)

Filter: ((p_brand = 'Brand#23'::bpchar) AND (p_container = 'MED CAN'::bpchar))

SubPlan 1

-> Aggregate (cost=135.45..135.46 rows=1 width=7) (actual time=0.347..0.347 rows=1 loops=61007)

-> Index Scan using lineitem_fk2 on lineitem (cost=0.00..135.37 rows=31 width=7) (actual time=0.034..0.304 rows=31 loops=61007)

Index Cond: (l_partkey = $0)

Total runtime: 50432.499 ms

(15 rows)

Time: 50434.793 ms

pgdb=#

第1页：一、数据库安装第2页：安装步骤第3页：二、PostgreSQL功能简介第4页：2.psql工具第5页：可以使用的SQL命令第6页：三、TPCH测试第7页：2.数据加载第8页：3.测试数据和查询语句的产生步骤第9页：创建索引的语句第10页：各组查询测试结果第11页：6.性能调整和优化第12页：explain analyse命令第13页：统计信息收集和管理

关注我们