From: | "Shenavai, Manuel" <manuel(dot)shenavai(at)sap(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | autoanalyze / autovacuum vs manually executed "vacuum analyze" |
Date: | 2024-06-11 06:55:03 |
Message-ID: | AM9PR02MB7410EC9CB4A14EFC78E3A993E8C72@AM9PR02MB7410.eurprd02.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2024-06-11 07:19:02 | Re: libpq v17 PQsocketPoll timeout is not granular enough |
Previous Message | Ron Johnson | 2024-06-11 04:40:11 | Re: Multiple tables row insertions from single psql input file |