After updating postgres, I noticed that one of the queries I was using became much slower. After running EXPLAIN ANALYZE I see that it is now using a different index on the same query.
Among other columns, my table has an applicationid column which is a foreign key BIGINT, and I have an attributes columns which is a jsonb key/value map.
The description on coupons table are (some irrelevant parts were omitted):
+------------------------+--------------------------+-------------------------------------------------------+
| Column | Type | Modifiers |
|------------------------+--------------------------+-------------------------------------------------------|
| id | integer | not null default nextval('coupons_id_seq'::regclass) |
| created | timestamp with time zone | not null default now() |
| campaignid | bigint | |
| value | text | |
| expirydate | timestamp with time zone | |
| startdate | timestamp with time zone | |
| attributes | jsonb | not null default '{}'::jsonb |
| applicationid | bigint | |
| deleted | timestamp with time zone | |
| deleted_changelogid | bigint | not null default 0 |
| accountid | bigint | not null |
| recipientintegrationid | text | |
+------------------------+--------------------------+-------------------------------------------------------+
Indexes:
"coupons_applicationid_value_idx" UNIQUE, btree (applicationid, value) WHERE deleted IS NULL
"coupons_attrs_index" gin (attributes)
"coupons_recipientintegrationid_idx" btree (recipientintegrationid)
"coupons_value_trgm_idx" gin (value gin_trgm_ops)
The query I'm running is (some irrelevant parts were omitted):
EXPLAIN ANALYZE SELECT
*,
COUNT(*) OVER () AS total_rows
FROM
coupons
WHERE
deleted IS NULL
AND coupons.applicationid = 2
AND coupons.attributes @> '{"SessionId":"1070695459"}'
ORDER BY
id ASC
LIMIT 1000;
applicationid doesn't help us much. The index that was previously used was coupons_attrs_index (over attributes column) which produced very good results.
After the update however, the query planner started preferring the index coupons_applicationid_value_idx for some reason!
Here is output from EXPLAIN ANALYZE (some irrelevant parts were omitted):
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| -> Sort (cost=64.09..64.10 rows=1 width=237) (actual time=3068.996..3068.996 rows=0 loops=1) |
| Sort Key: coupons.id |
| Sort Method: quicksort Memory: 25kB |
| -> WindowAgg (cost=0.86..64.08 rows=1 width=237) (actual time=3068.988..3068.988 rows=0 loops=1) |
| -> Nested Loop (cost=0.86..64.07 rows=1 width=229) (actual time=3068.985..3068.985 rows=0 loops=1) |
| -> Index Scan using coupons_applicationid_value_idx on coupons (cost=0.43..61.61 rows=1 width=213) (actual time=3068.984..3068.984 rows=0 loops=1) |
| Index Cond: (applicationid = 2) |
| Filter: (attributes @> '{"SessionId": "1070695459"}'::jsonb) |
| Rows Removed by Filter: 2344013 |
| Planning Time: 0.531 ms |
| Execution Time: 3069.076 ms |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN
Time: 3.159s (3 seconds), executed in: 3.102s (3 seconds)
Can anyone help me understand why the query planner uses a less efficient index (coupons_applicationid_value_idx instead of coupons_attrs_index) after the update?
After adding a mixed (BTREE + GIN) index on (applicationid, attributes) that index was selected effectively solving the issue. I would still like to understand what happened to predict issues like this one in the future.
[EDIT 31-01-20 11:02]: The issue returned after 24 hours. Again the wrong index was chosen by the planner and the query became slow. Running a simple analyze solved it.
It is still very strange that it only started happening after the update to PG 11.