Hello! We have an issue with database planner choosing really expensive
sequence scan instead of an index scan in some cases.
I'm reaching out in order to maybe get some idea on what we're dealing with /
what could be the actual issue here.
The table affected is the users table with a field called "private_metadata"
which is JSONB type.
There are currently two GIN indexes on that table - one GIN and second which is
GIN jsonb_path_ops.
The operator that is used for the problematic query is contains (@>) so
jsonb_path_ops should be preferred according to docs
https://www.postgresql.org/docs/11/gin-builtin-opclasses.html.
However the database sometimes decides NOT to use the index and perform the
sequence scan.
I have observed it happens in intervals - ex. 10 hours everything is ok, then
something snaps and there are sequence scans for few more hours, then back to
index and so on.
More context:
- Database version: 11.18
- The table schema with indexes
CREATE TABLE account_user (
id integer NOT NULL,
private_metadata jsonb,
);
CREATE INDEX user_p_meta_idx ON account_user USING gin (private_metadata);
CREATE INDEX user_p_meta_jsonb_path_idx ON account_user USING gin
(private_metadata jsonb_path_ops);
- The query that is perfomed
SELECT "account_user"."id", "account_user"."private_metadata"
FROM "account_user"
WHERE "account_user"."private_metadata" @ > '{"somekey": "somevalue"}'
LIMIT 21;
- Plan when it uses an index
{
"Plan": {
"Node Type": "Limit",
"Parallel Aware": false,
"Startup Cost": 1091.29,
"Total Cost": 1165.26,
"Plan Rows": 21,
"Plan Width": 4,
"Plans": [
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "account_user",
"Alias": "account_user",
"Startup Cost": 1091.29,
"Total Cost": 17129.12,
"Plan Rows": 4553,
"Plan Width": 4,
"Recheck Cond": "( private_metadata @ > ? :: jsonb )",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Index Name": "user_p_meta_jsonb_path_idx",
"Startup Cost": 0,
"Total Cost": 1090.15,
"Plan Rows": 4553,
"Plan Width": 0,
"Index Cond": "( private_metadata @ > ? :: jsonb )"
}
]
}
]
}
}
- Plan when it doesn't use an index
{
"Plan": {
"Node Type": "Limit",
"Parallel Aware": false,
"Startup Cost": 0,
"Total Cost": 1184.3,
"Plan Rows": 21,
"Plan Width": 4,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "account_user",
"Alias": "account_user",
"Startup Cost": 0,
"Total Cost": 256768.27,
"Plan Rows": 4553,
"Plan Width": 4,
"Filter": "( private_metadata @ > ? :: jsonb )"
}
]
}
}
- There are ~4.5M rows on the table
- We currently have a script that is heavily inserting rows 24/7, about 130k
rows / day (1,5 row/s)
- It seems maybe the index can't keep up(?) because of this heavy insertion
SELECT * FROM pgstatginindex('user_p_meta_jsonb_path_idx');
version | pending_pages | pending_tuples
-+---+
2 |98 | 28807
(1 row)
Might it be the case that is cloggs up and cannot use the index when reading?
- Last autovacuum for some reason happened 4 days ago
select * from pg_stat_user_tables where relname = 'account_user';
-[ RECORD 1
]---+-
relid | 74937
schemaname | public
relname | account_user
seq_scan| 66578
seq_tup_read| 99542628744
idx_scan| 9342647
idx_tup_fetch | 105527685
n_tup_ins | 518901
n_tup_upd | 684607
n_tup_del | 25
n_tup_hot_upd | 82803
n_live_tup | 4591412
n_dead_tup | 370828
n_mod_since_analyze | 152968
last_vacuum |
last_autovacuum | 2023-10-13 07: 35: 29.11448+00
last_analyze|
last_autoanalyze| 2023-10-13 07: 44: 31.90437+00
vacuum_count| 0
autovacuum_count| 2
analyze_count | 0
- The plans above comes from a production system, I failed to reproduce it
locally, only two cases in which I managed to get a seq scan were:
a) when there are very few rows in the table
b) when I run the filter @> '{}' in which case I suppose postgres deduces "you
want everything" so additional index lookup in not necessary (checked and this
filter is impossible to induce by our code).
Any feedback is appreciated! What would be the possible reason the planner
chooses seq scan in that case?
