On Tue, Oct 17, 2023 at 10:09 AM Tomasz Szymański <lime129@gmail.com> wrote:
> - Database version: 11.18
That is pretty old. It is 3 bug-fix releases out of date even for its major version, and the major version itself is just about to reach EOL and is missing relevant improvements.
- Plan when it uses an index "Total Cost": 1165.26, - Plan when it doesn't use an index "Total Cost": 1184.3,
The JSON format for plans is pretty non-ideal for human inspection; especially so once you include ANALYZE and BUFFERS, which you should do. Please use the plain text format instead. But I can see that the plans are very similar in cost, so it wouldn't take much to shift between them. Should we assume that not using the index is much slower (otherwise, why would you be asking the question?)?
- 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?
Definitely possible. The planner does take those numbers into account when planning. The easiest thing would be to just turn off fastupdate for those indexes. That might make the INSERTs somewhat slower (it is hard to predict how much and you haven't complained about the performance of the INSERTs anyway) but should make the SELECTs more predictable and generally faster. I habitually turn fastupdate off and then turn it back on only if I have an identifiable cause to do so.
If you don't want to turn fastupdate off, you could instead change the table's autovac parameters to be more aggressive (particularly n_ins_since_vacuum, except that that doesn't exist until v13), or have a cron job call gin_clean_pending_list periodically.
- Last autovacuum for some reason happened 4 days ago
n_live_tup | 4591412 n_dead_tup | 370828
Based on those numbers and default parameters, there is no reason for it to be running any sooner. That reflects only 8% turnover while the default factor is 20%.