Query on postgres 9.5 several times slower than on postgres 9.1

Question

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 the other postgres 9.5. There are only slight differences in the data are caused only by slightly different timings (time ordering) of concurrent insertions, and they should not be significant (less than 0.1 percent of the number of rows). The postgres configuration on both system is the same (given below).

The query, the database schema and the query plans both on postgres 9.5 and 9.1 are also given below.

I am consistently getting several times slower query execution on postgres postgres 9.5: Execution time: 280.777 ms postgres 9.1: Total runtime: 66.566 ms which I can not understand.

In this case postgres 9.5 is several times slower. It uses a different query plan compared to the postgres 9.1. Any help with explaining why the performance is different, or directions where to look into will be greatly appreciated.

Postgres configuration

The configuration of postgres 9.1 is:

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

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

The configuration of the postgres 9.5 is:

------------------------------
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 information:

                            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

The query plan on 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

The query plan on 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)

user_6331875 · Answer

work_mem = 80MB

By assigning more memory to this parameter you can indeed speed some queries by performing larger operations in-memory but it can also encourage the query planner to choose less optimized paths. Try to tweak/decrease this parameter and rerun the query planner.

Correspondingly you might want to run vacuum analyze on your 9.5 database.

user_9904105 · Answer

I believe you would have noticed the query plan has changed significantly b/w 9.5 and 9.1

It seems to me there are 2 pronged issues with the new query plan.

Too Many rows, refer -
->  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

This leads to high I/O and high CPU usage (to sort) both are useless towards the end goal (refer the line -

                     Rows Removed by Join Filter: 655
                           Rows Removed by Join Filter: 1956
                                   Rows Removed by Join Filter: 5216

There have been few changes in 9.1 to 9.5 towards query planning, but not sure which one has impacted in here.

The solution as listed in URL - https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/ works on 9.1 (refer the line in URL and your query plan for 9.1 -

->  Values Scan on "*VALUES*"

If you can share the data set for other tables, it would be easier to find the which join/where clause caused this.