On Tue, Oct 17, 2023 at 10:09 AM Tomasz Szymański <lime...@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%.
Cheers,
Jeff
