个人中心
文章发布
退出
作者热门文章
- html - 出于某种原因，IE8 对我的 Sass 文件中继承的 html5 CSS 不友好？
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景？
25
4
我正在两个具有相同 postgres 数据库的系统上运行性能测试，用相同的数据。一个系统有 postgres 9.1，另一个有 postgres 9.5。数据中只有细微的差异只是由于并发的时间(时间顺序)略有不同造成的插入，并且它们不应很重要(小于行数的 0.1%)。两个系统上的 postgres 配置相同(如下所示)。
下面还给出了 postgres 9.5 和 9.1 上的查询、数据库模式和查询计划。
我在 postgres 上的查询执行速度一直慢了好几倍postgres 9.5:执行时间:280.777 毫秒postgres 9.1:总运行时间:66.566 毫秒我无法理解。
在这种情况下，postgres 9.5 会慢好几倍。与 postgres 9.1 相比，它使用不同的查询计划。任何有助于解释为什么性能不同的帮助，或者将不胜感激。
postgres 9.1的配置是:
------------------------------------------------------------------------------
shared_buffers = 2048MB
temp_buffers = 8MB
work_mem = 18MB
maintenance_work_mem = 512MB
max_stack_depth = 2MB
------------------------------------------------------------------------------
wal_level = minimal
wal_buffers = 8MB
checkpoint_segments = 32
checkpoint_timeout = 5min
checkpoint_completion_target = 0.9
checkpoint_warning = 30s
------------------------------------------------------------------------------
effective_cache_size = 5120MB
postgres 9.5 的配置是:
------------------------------
shared_buffers = 2048MB
temp_buffers = 8MB
work_mem = 80MB
maintenance_work_mem = 512MB
dynamic_shared_memory_type = posix
------------------------------
wal_level = minimal
wal_buffers = 8MB
max_wal_size = 5GB
wal_keep_segments = 32
effective_cache_size = 4GB
default_statistics_target = 100
------------------------------
EXPLAIN ANALYZE SELECT bricks.id AS gid,TIMESTAMP '2017-07-03 06:00:00' AS time,
intensity, bricks.weather_type, polygons.geometry AS geom,
starttime, endtime, ST_AsGeoJSON(polygons.geometry) as geometry_json,
weather_types.name as weather_type_literal, notification_type, temp.level as level
FROM bricks
INNER JOIN weather_types
ON weather_types.id = bricks.weather_type
INNER JOIN polygons
ON bricks.polygon_id=polygons.id
JOIN notifications
ON bricks.notification_id = notifications.id
JOIN bricks_notif_type_priority prio
ON (bricks.polygon_id = prio.polygon_id
AND bricks.weather_type = prio.weather_type
AND notifications.notification_type = prio.priority_notif_type)
JOIN (VALUES (14, 1),
(4, 2),
(5, 3),
(1, 4),
(2, 5),
(3, 6),
(13, 7),
(15,8)) as order_list (id, ordering)
ON bricks.weather_type = order_list.id
JOIN (VALUES
(15,1,1,2,'warning'::notification_type_enum),
(15,2,2,3,'warning'::notification_type_enum),
(15,3,3,999999,'warning'::notification_type_enum),
(13,1,1,2,'warning'::notification_type_enum),
(13,2,2,3,'warning'::notification_type_enum),
(13,3,3,99999,'warning'::notification_type_enum),
(5,1,1,3,'warning'::notification_type_enum),
(5,2,3,5,'warning'::notification_type_enum),
(5,3,5,99999,'warning'::notification_type_enum),
(4,1,15,25,'warning'::notification_type_enum),
(4,2,25,35,'warning'::notification_type_enum),
(4,3,35,99999,'warning'::notification_type_enum),
(3,1,75,100,'warning'::notification_type_enum),
(3,2,100,130,'warning'::notification_type_enum),
(3,3,130,99999,'warning'::notification_type_enum),
(2,1,30,50,'warning'::notification_type_enum),
(2,2,50,100,'warning'::notification_type_enum),
(2,3,100,99999,'warning'::notification_type_enum),
(1,1,18,50,'warning'::notification_type_enum),
(1,2,50,300,'warning'::notification_type_enum),
(1,3,300,999999,'warning'::notification_type_enum),
(15,1,1,2,'autowarn'::notification_type_enum),
(15,2,2,3,'autowarn'::notification_type_enum),
(15,3,3,999999,'autowarn'::notification_type_enum),
(13,1,1,2,'autowarn'::notification_type_enum),
(13,2,2,3,'autowarn'::notification_type_enum),
(13,3,3,99999,'autowarn'::notification_type_enum),
(5,1,10,20,'autowarn'::notification_type_enum),
(5,2,20,50,'autowarn'::notification_type_enum),
(5,3,50,99999,'autowarn'::notification_type_enum),
(4,1,15,25,'autowarn'::notification_type_enum),
(4,2,25,35,'autowarn'::notification_type_enum),
(4,3,35,99999,'autowarn'::notification_type_enum),
(3,1,75,100,'autowarn'::notification_type_enum),
(3,2,100,130,'autowarn'::notification_type_enum),
(3,3,130,99999,'autowarn'::notification_type_enum),
(2,1,30,50,'autowarn'::notification_type_enum),
(2,2,50,100,'autowarn'::notification_type_enum),
(2,3,100,99999,'autowarn'::notification_type_enum),
(1,1,18,50,'autowarn'::notification_type_enum),
(1,2,50,300,'autowarn'::notification_type_enum),
(1,3,300,999999,'autowarn'::notification_type_enum),
(15,1,1,2,'forewarn'::notification_type_enum),
(15,2,2,3,'forewarn'::notification_type_enum),
(15,3,3,999999,'forewarn'::notification_type_enum),
(13,1,1,2,'forewarn'::notification_type_enum),
(13,2,2,3,'forewarn'::notification_type_enum),
(13,3,3,99999,'forewarn'::notification_type_enum),
(5,1,1,3,'forewarn'::notification_type_enum),
(5,2,3,5,'forewarn'::notification_type_enum),
(5,3,5,99999,'forewarn'::notification_type_enum),
(4,1,15,25,'forewarn'::notification_type_enum),
(4,2,25,35,'forewarn'::notification_type_enum),
(4,3,35,99999,'forewarn'::notification_type_enum),
(3,1,75,100,'forewarn'::notification_type_enum),
(3,2,100,130,'forewarn'::notification_type_enum),
(3,3,130,99999,'forewarn'::notification_type_enum),
(2,1,30,50,'forewarn'::notification_type_enum),
(2,2,50,100,'forewarn'::notification_type_enum),
(2,3,100,99999,'forewarn'::notification_type_enum),
(1,1,18,50,'forewarn'::notification_type_enum),
(1,2,50,300,'forewarn'::notification_type_enum),
(1,3,300,999999,'forewarn'::notification_type_enum),
(15,1,1,2,'auto-forewarn'::notification_type_enum),
(15,2,2,3,'auto-forewarn'::notification_type_enum),
(15,3,3,999999,'auto-forewarn'::notification_type_enum),
(13,1,1,2,'auto-forewarn'::notification_type_enum),
(13,2,2,3,'auto-forewarn'::notification_type_enum),
(13,3,3,99999,'auto-forewarn'::notification_type_enum),
(5,1,10,20,'auto-forewarn'::notification_type_enum),
(5,2,20,50,'auto-forewarn'::notification_type_enum),
(5,3,50,99999,'auto-forewarn'::notification_type_enum),
(4,1,15,25,'auto-forewarn'::notification_type_enum),
(4,2,25,35,'auto-forewarn'::notification_type_enum),
(4,3,35,99999,'auto-forewarn'::notification_type_enum),
(3,1,75,100,'auto-forewarn'::notification_type_enum),
(3,2,100,130,'auto-forewarn'::notification_type_enum),
(3,3,130,99999,'auto-forewarn'::notification_type_enum),
(2,1,30,50,'auto-forewarn'::notification_type_enum),
(2,2,50,100,'auto-forewarn'::notification_type_enum),
(2,3,100,99999,'auto-forewarn'::notification_type_enum),
(1,1,18,50,'auto-forewarn'::notification_type_enum),
(1,2,50,300,'auto-forewarn'::notification_type_enum),
(1,3,300,999999,'auto-forewarn'::notification_type_enum)
) AS temp (weather_type,level,min,max,ntype)
ON bricks.weather_type = temp.weather_type
AND notifications.notification_type=temp.ntype
AND intensity >= temp.min
AND intensity < temp.max
AND temp.level in (1,2,3)
WHERE polygons.set = 0
AND '2017-07-03 06:00:00' BETWEEN starttime AND endtime
AND weather_types.name in ('rain','snowfall','storm','freezingRain','thunderstorm')
ORDER BY
(CASE notifications.notification_type
WHEN 'forewarn' THEN 0
WHEN 'auto-forewarn' then 0
ELSE 1
END) ASC,
temp.level ASC,
order_list.ordering DESC
LIMIT 10000000
Table "public.notifications"
Column | Type | Modifiers
-------------------+-----------------------------+------------------------------------------------------------
id | integer | not null default nextval('notifications_id_seq'::regclass)
weather_type | integer |
macro_blob | bytea | not null
logproxy_id | integer | not null
sent_time | timestamp without time zone | not null
max_endtime | timestamp without time zone | not null
notification_type | notification_type_enum | not null
Indexes:
"notifications_pkey" PRIMARY KEY, btree (id)
"notifications_unique_logproxy_id" UNIQUE CONSTRAINT, btree (logproxy_id)
"notifications_max_endtime_idx" btree (max_endtime)
"notifications_weather_type_idx" btree (weather_type)
Foreign-key constraints:
"notifications_weather_type_fkey" FOREIGN KEY (weather_type) REFERENCES weather_types(id) ON DELETE CASCADE
Referenced by:
TABLE "bricks_notif_extent" CONSTRAINT "bricks_notif_extent_notification_id_fkey" FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
TABLE "bricks" CONSTRAINT "bricks_notification_id_fkey" FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
Table "public.bricks"
Column | Type | Modifiers
-----------------+-----------------------------+-----------------------------------------------------
id | bigint | not null default nextval('bricks_id_seq'::regclass)
polygon_id | integer |
terrain_types | integer | not null
weather_type | integer |
intensity | integer | not null
starttime | timestamp without time zone | not null
endtime | timestamp without time zone | not null
notification_id | integer |
Indexes:
"bricks_pkey" PRIMARY KEY, btree (id)
"bricks_notification_id_idx" btree (notification_id)
"bricks_period_idx" gist (cube(date_part('epoch'::text, starttime), date_part('epoch'::text, endtime)))
"bricks_polygon_idx" btree (polygon_id)
"bricks_weather_type_idx" btree (weather_type)
Foreign-key constraints:
"bricks_notification_id_fkey" FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
"bricks_polygon_id_fkey" FOREIGN KEY (polygon_id) REFERENCES polygons(id) ON DELETE CASCADE
"bricks_weather_type_fkey" FOREIGN KEY (weather_type) REFERENCES weather_types(id) ON DELETE CASCADE
Table "public.polygons"
Column | Type | Modifiers
----------+-----------------------+-------------------------------------------------------
id | integer | not null default nextval('polygons_id_seq'::regclass)
country | character(2) | not null
set | integer | not null
geometry | geometry |
zone_id | character varying(32) |
Indexes:
"polygons_pkey" PRIMARY KEY, btree (id)
"polygons_geometry_idx" gist (geometry)
"polygons_zone_id_idx" btree (zone_id)
Check constraints:
"enforce_dims_geometry" CHECK (st_ndims(geometry) = 2)
"enforce_geotype_geometry" CHECK (geometrytype(geometry) = 'MULTIPOLYGON'::text OR geometry IS NULL)
"enforce_srid_geometry" CHECK (st_srid(geometry) = 4326)
Referenced by:
TABLE "bricks_notif_type_priority" CONSTRAINT "bricks_notif_type_priority_polygon_id_fkey" FOREIGN KEY (polygon_id) REFERENCES polygons(id) ON DELETE CASCADE
TABLE "bricks" CONSTRAINT "bricks_polygon_id_fkey" FOREIGN KEY (polygon_id) REFERENCES polygons(id) ON DELETE CASCADE
TABLE "polygon_contains" CONSTRAINT "polygon_contains_contained_id_fkey" FOREIGN KEY (contained_id) REFERENCES polygons(id) ON DELETE CASCADE
TABLE "polygon_contains" CONSTRAINT "polygon_contains_id_fkey" FOREIGN KEY (id) REFERENCES polygons(id) ON DELETE CASCADE
Table "public.weather_types"
Column | Type | Modifiers
--------+-----------------------+------------------------------------------------------------
id | integer | not null default nextval('weather_types_id_seq'::regclass)
name | character varying(32) | not null
Indexes:
"weather_types_pkey" PRIMARY KEY, btree (id)
"weather_type_unique_name" UNIQUE CONSTRAINT, btree (name)
Referenced by:
TABLE "bricks_notif_type_priority" CONSTRAINT "bricks_notif_type_priority_weather_type_fkey" FOREIGN KEY (weather_type) REFERENCES weather_types(id) ON DELETE CASCADE
TABLE "bricks" CONSTRAINT "bricks_weather_type_fkey" FOREIGN KEY (weather_type) REFERENCES weather_types(id) ON DELETE CASCADE
TABLE "notifications" CONSTRAINT "notifications_weather_type_fkey" FOREIGN KEY (weather_type) REFERENCES weather_types(id) ON DELETE CASCADE
Table "public.bricks_notif_type_priority"
Column | Type | Modifiers
---------------------+------------------------+-------------------------------------------------------------------------
id | integer | not null default nextval('bricks_notif_type_priority_id_seq'::regclass)
polygon_id | integer | not null
weather_type | integer | not null
priority_notif_type | notification_type_enum | not null
Indexes:
"bricks_notif_type_priority_pkey" PRIMARY KEY, btree (id)
"bricks_notif_type_priority_poly_idx" btree (polygon_id, weather_type)
Foreign-key constraints:
"bricks_notif_type_priority_polygon_id_fkey" FOREIGN KEY (polygon_id) REFERENCES polygons(id) ON DELETE CASCADE
"bricks_notif_type_priority_weather_type_fkey" FOREIGN KEY (weather_type) REFERENCES weather_types(id) ON DELETE CASCADE
Limit (cost=1339.71..1339.72 rows=1 width=2083) (actual time=280.390..280.429 rows=224 loops=1)
-> Sort (cost=1339.71..1339.72 rows=1 width=2083) (actual time=280.388..280.404 rows=224 loops=1)
Sort Key: (CASE notifications.notification_type WHEN 'forewarn'::notification_type_enum THEN 0 WHEN 'auto-forewarn'::notification_type_enum THEN 0 ELSE 1 END), "*VALUES*_1".column2, "*VALUES*".column2 DESC
Sort Method: quicksort Memory: 929kB
-> Nested Loop (cost=437.79..1339.70 rows=1 width=2083) (actual time=208.373..278.926 rows=224 loops=1)
Join Filter: (bricks.polygon_id = polygons.id)
-> Nested Loop (cost=437.50..1339.30 rows=1 width=62) (actual time=186.122..221.536 rows=307 loops=1)
Join Filter: ((weather_types.id = prio.weather_type) AND (notifications.notification_type = prio.priority_notif_type))
Rows Removed by Join Filter: 655
-> Nested Loop (cost=437.08..1336.68 rows=1 width=74) (actual time=5.522..209.237 rows=652 loops=1)
Join Filter: ("*VALUES*_1".column5 = notifications.notification_type)
Rows Removed by Join Filter: 1956
-> Merge Join (cost=436.66..1327.38 rows=4 width=74) (actual time=5.277..195.569 rows=2608 loops=1)
Merge Cond: (bricks.weather_type = weather_types.id)
-> Nested Loop (cost=435.33..1325.89 rows=28 width=60) (actual time=5.232..193.652 rows=2608 loops=1)
Join Filter: ((bricks.intensity >= "*VALUES*_1".column3) AND (bricks.intensity < "*VALUES*_1".column4))
Rows Removed by Join Filter: 5216
-> Merge Join (cost=1.61..1.67 rows=1 width=28) (actual time=0.093..0.181 rows=84 loops=1)
Merge Cond: ("*VALUES*".column1 = "*VALUES*_1".column1)
-> Sort (cost=0.22..0.24 rows=8 width=8) (actual time=0.022..0.024 rows=8 loops=1)
Sort Key: "*VALUES*".column1
Sort Method: quicksort Memory: 25kB
-> Values Scan on "*VALUES*" (cost=0.00..0.10 rows=8 width=8) (actual time=0.005..0.007 rows=8 loops=1)
-> Sort (cost=1.39..1.40 rows=3 width=20) (actual time=0.067..0.097 rows=84 loops=1)
Sort Key: "*VALUES*_1".column1
Sort Method: quicksort Memory: 31kB
-> Values Scan on "*VALUES*_1" (cost=0.00..1.36 rows=3 width=20) (actual time=0.009..0.041 rows=84 loops=1)
Filter: (column2 = ANY ('{1,2,3}'::integer[]))
-> Bitmap Heap Scan on bricks (cost=433.72..1302.96 rows=1417 width=40) (actual time=0.568..2.277 rows=93 loops=84)
Recheck Cond: (weather_type = "*VALUES*".column1)
Filter: (('2017-07-03 06:00:00'::timestamp without time zone >= starttime) AND ('2017-07-03 06:00:00'::timestamp without time zone <= endtime))
Rows Removed by Filter: 6245
Heap Blocks: exact=18528
-> Bitmap Index Scan on bricks_weather_type_idx (cost=0.00..433.37 rows=8860 width=0) (actual time=0.536..0.536 rows=6361 loops=84)
Index Cond: (weather_type = "*VALUES*".column1)
-> Sort (cost=1.33..1.35 rows=5 width=14) (actual time=0.040..0.418 rows=2609 loops=1)
Sort Key: weather_types.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on weather_types (cost=0.00..1.28 rows=5 width=14) (actual time=0.009..0.013 rows=5 loops=1)
Filter: ((name)::text = ANY ('{rain,snowfall,storm,freezingRain,thunderstorm}'::text[]))
Rows Removed by Filter: 12
-> Index Scan using notifications_pkey on notifications (cost=0.41..2.31 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=2608)
Index Cond: (id = bricks.notification_id)
-> Index Scan using bricks_notif_type_priority_poly_idx on bricks_notif_type_priority prio (cost=0.42..2.61 rows=1 width=12) (actual time=0.017..0.017 rows=1 loops=652)
Index Cond: ((polygon_id = bricks.polygon_id) AND (weather_type = bricks.weather_type))
-> Index Scan using polygons_pkey on polygons (cost=0.29..0.38 rows=1 width=2033) (actual time=0.021..0.022 rows=1 loops=307)
Index Cond: (id = prio.polygon_id)
Filter: (set = 0)
Rows Removed by Filter: 0
Planning time: 27.326 ms
Execution time: 280.777 ms
Limit (cost=2486.29..2486.30 rows=1 width=8219) (actual time=66.273..66.301 rows=224 loops=1)
-> Sort (cost=2486.29..2486.30 rows=1 width=8219) (actual time=66.272..66.281 rows=224 loops=1)
Sort Key: (CASE notifications.notification_type WHEN 'forewarn'::notification_type_enum THEN 0 WHEN 'auto-forewarn'::notification_type_enum THEN 0 ELSE 1 END), "*VALUES*".column2, "*VALUES*".column2
Sort Method: quicksort Memory: 1044kB
-> Nested Loop (cost=171.27..2486.28 rows=1 width=8219) (actual time=22.064..65.335 rows=224 loops=1)
Join Filter: ((bricks.intensity >= "*VALUES*".column3) AND (bricks.intensity < "*VALUES*".column4) AND (weather_types.id = "*VALUES*".column1) AND (notifications.notification_type = "*VALUES*".column5))
-> Nested Loop (cost=171.27..2484.85 rows=1 width=8231) (actual time=16.632..24.503 rows=224 loops=1)
Join Filter: (prio.priority_notif_type = notifications.notification_type)
-> Nested Loop (cost=171.27..2482.66 rows=1 width=8231) (actual time=3.318..22.309 rows=681 loops=1)
-> Nested Loop (cost=171.27..2479.52 rows=1 width=98) (actual time=3.175..19.252 rows=962 loops=1)
-> Nested Loop (cost=171.27..2471.14 rows=1 width=86) (actual time=3.144..14.751 rows=652 loops=1)
-> Hash Join (cost=0.20..29.73 rows=1 width=46) (actual time=0.025..0.039 rows=5 loops=1)
Hash Cond: (weather_types.id = "*VALUES*".column1)
-> Seq Scan on weather_types (cost=0.00..29.50 rows=5 width=38) (actual time=0.007..0.015 rows=5 loops=1)
Filter: ((name)::text = ANY ('{rain,snowfall,storm,freezingRain,thunderstorm}'::text[]))
-> Hash (cost=0.10..0.10 rows=8 width=8) (actual time=0.006..0.006 rows=8 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Values Scan on "*VALUES*" (cost=0.00..0.10 rows=8 width=8) (actual time=0.002..0.004 rows=8 loops=1)
-> Bitmap Heap Scan on bricks (cost=171.07..2352.98 rows=7074 width=40) (actual time=0.718..2.917 rows=130 loops=5)
Recheck Cond: (weather_type = weather_types.id)
Filter: (('2017-07-03 06:00:00'::timestamp without time zone >= starttime) AND ('2017-07-03 06:00:00'::timestamp without time zone <= endtime))
-> Bitmap Index Scan on bricks_weather_type_idx (cost=0.00..170.71 rows=8861 width=0) (actual time=0.644..0.644 rows=8906 loops=5)
Index Cond: (weather_type = weather_types.id)
-> Index Scan using bricks_notif_type_priority_poly_idx on bricks_notif_type_priority prio (cost=0.00..8.37 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=652)
Index Cond: ((polygon_id = bricks.polygon_id) AND (weather_type = weather_types.id))
-> Index Scan using polygons_pkey on polygons (cost=0.00..3.13 rows=1 width=8145) (actual time=0.003..0.003 rows=1 loops=962)
Index Cond: (id = bricks.polygon_id)
Filter: (set = 0)
-> Index Scan using notifications_pkey on notifications (cost=0.00..2.17 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=681)
Index Cond: (id = bricks.notification_id)
-> Values Scan on "*VALUES*" (cost=0.00..1.36 rows=3 width=20) (actual time=0.001..0.028 rows=84 loops=224)
Filter: (column2 = ANY ('{1,2,3}'::integer[]))
Total runtime: 66.566 ms
(33 rows)
最佳答案
work_mem = 80MB
通过为这个参数分配更多的内存，你确实可以通过在内存中执行更大的操作来加速一些查询，但它也可以鼓励查询规划器选择不太优化的路径。尝试调整/减少此参数并重新运行查询规划器。
相应地，您可能希望在 9.5 数据库上运行真空分析。
关于postgresql - 在 postgres 9.5 上查询比在 postgres 9.1 上慢几倍，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/44908485/
25
4
0
我是一名优秀的程序员,十分优秀！