Postgres 9.5 query is several times slower than postgres 9.1
I am running performance tests on two systems that have the same postgres database, with the same data. One system has postgres 9.1 and others have postgres 9.5. There are only minor differences in the data, caused only by slightly different timings (temporal ordering) of simultaneous inserts, and they should not be significant (less than 0.1 percent of the row count). The postgres configuration is the same on both systems (below).
The query, database schema, and query plans for both postgres 9.5 and 9.1 are also shown below.
I am consistently getting several times slower query execution in postgres postgres 9.5: Execution time: 280.777 ms postgres 9.1: Total execution time: 66.566 ms which I cannot figure out.
In this case, postgres 9.5 is several times slower. It uses a different query plan compared to postgres 9.1. Any help explaining why the performance is different or directions to look into would be greatly appreciated.
Postgres configuration
Postgres 9.1 config:
------------------------------------------------------------------------------ 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 config:
------------------------------ 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 ------------------------------
QUERY
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:
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
Query plan for postgres 9.5:
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
Query plan for postgres 9.1:
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)
+3
source to share
1 answer
work_mem = 80MB
By assigning more memory to this parameter, you can indeed speed up some queries by performing large operations in RAM, but it can also force the query planner to choose less optimized paths. Try adjusting / decreasing this setting and rerunning the query scheduler.
Accordingly, you may want to do vacuum analysis on your 9.5 database.
0
source to share