On Mon, Feb 20, 2023 at 9:43 PM Mikhail Balayan wrote:
> What catches my eye: scanning indexes smaller than 3.1GB is fast, larger ones
> are slow. For example:
> idx_applications2_policy_id is 3131 MB took just 5 seconds (DETAIL: CPU:
> user: 2.99 s, system: 1.65 s, elapsed: 5.32 s)
> but idx_a
Probably the result of vacuum freeze manual execution will give some more
information:
Table size:
appdbname3=# \dt+ appschemaname.applications
List of relations
Schema | Name | Type | Owner | Size |
Description
---+
>
> >> Can you run amcheck's bt_index_check() routine against some of the
> >> indexes you've shown? There is perhaps some chance that index
> >> corruption exists and causes VACUUM to take a very long time to delete
> >> index pages. This is pretty much a wild guess, though.
Unfortunately I can'
>
> >> Even still,
> >> the information about buffers that you've shown does indeed appear to
> >> be total nonsense (while everything else we can see looks plausible).
Actually not only buffers. The elapsed time also looks strange. And this is
the first reason why I paid attention to this situati
Adrian,
thanks for pointing out the fix. We are just about to update to 11.18 next
month.
Mikhael
On Thu, 16 Feb 2023 at 23:44, Adrian Klaver
wrote:
> On 2/15/23 22:57, Mikhail Balayan wrote:
> > Hello,
> >
> > I have a big table in the actively working system, in which nothing is
> > written f
On Thu, Feb 16, 2023 at 5:40 PM Mikhail Balayan wrote:
>> >> Do you have any non-btree indexes on the table? Can you show us the
>> >> details of the
>> >> table, including all of its indexes? In other words, can you show "\d
>> >> applications" output from psql?
>
> Only btree indexes. Please f
On Thu, Feb 16, 2023 at 7:44 AM Adrian Klaver wrote:
> > That is, if I understand it correctly, it says that there were (and
> > actually are) 2013128 pages of which 2008230 were skipped, which leaves
> > 4898 blocks to be scanned. I.e. it seems that the allocated 1GB
> > (autovacuum_work_mem) sho
On 2/15/23 22:57, Mikhail Balayan wrote:
Hello,
I have a big table in the actively working system, in which nothing is
written for a long time, and nothing is read from it. Table size is 15GB
(data only), indexes 150GB.
Since the table does not change, after a while it crosses the
autovacuum_
Hello,
I have a big table in the actively working system, in which nothing is
written for a long time, and nothing is read from it. Table size is 15GB
(data only), indexes 150GB.
Since the table does not change, after a while it crosses the
autovacuum_freeze_max_age and an aggressive vacuum is tri
