public inbox for [email protected]  

help / color / mirror / Atom feed

From: Shenavai, Manuel <[email protected]>
To: pgsql-general <[email protected]>
Subject: autoanalyze / autovacuum vs manually executed "vacuum analyze"
Date: Tue, 11 Jun 2024 06:55:03 +0000
Message-ID: <AM9PR02MB7410EC9CB4A14EFC78E3A993E8C72@AM9PR02MB7410.eurprd02.prod.outlook.com> (raw)

Hi everyone,

we created an index on a table to improve the performance for a SQL statement. After executing “vacuum analyze mytable”, the index is used. I know that there is also an autovacuum/autoanalyzer configured. I can see that autovacuum and autoanalyzer ran recently. But the index is still not used. I would expect that the index is used after the autovacuum/autoanalyze.

Questions:

  1.  Why would the index not be used after autovacuum/autoanalyze but only after manually running vacuum analyze?
  2.  Is there any difference between the autovacuum /autoanalyze and the manual exected “vacuum analyze”?

Details:

1) Get last analyze and last autoanalyzer dates:
select * from pg_stat_all_tables where relname ='mytable'
>"last_analyze": "2024-05-07T15:26:01.363796+00:00",
>"last_autoanalyze": "2024-06-09T20:52:32.411717+00:00",
>"last_autovacuum": "2024-05-20T02:14:34.165689+00:00",
>"last_vacuum": "2024-05-07T15:24:42.644449+00:00",

2) Explain analyze <SQL statement>: no index is used for the SQL statement

3) vacuum analyze mytable -- manually executed

4) Explain analyze <SQL statement>: index is now used ✓

Thanks in advance &
Best regards,
Manuel

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: autoanalyze / autovacuum vs manually executed "vacuum analyze"
  In-Reply-To: <AM9PR02MB7410EC9CB4A14EFC78E3A993E8C72@AM9PR02MB7410.eurprd02.prod.outlook.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox