Search Postgresql Archives

autoanalyze / autovacuum vs manually executed "vacuum analyze"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

 

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux