How to efficiently vacuum analyze tables in Postgres

sql postgresql vacuum
Ramanan picture Ramanan · May 16, 2016 · Viewed 10.6k times · Source

I had a huge query running on postgres and one of the join tables always did a sequential scan. The index on the column exists and postgres just didn't use it. I did a vacuum analyze and then postgres query plan immediately changes to doing an index scan.

My question is what is the most efficient way to be doing a vacuum analyze. Does it lock tables ? If it does, how do you vacuum analyze live production tables ?

Answer

lsilva picture lsilva · May 16, 2016

You can run analyze only, no need to run vacuum also. Sintax will be :

ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]

In the documentation it states :

ANALYZE requires only a read lock on the target table, so it can run in parallel with other activity on the table.

You can find more information here:

http://www.postgresql.org/docs/9.4/static/sql-analyze.html

https://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT

Does a vacuum analyze lock tables ?

No, it's the "FULL VACUUM" command that locks tables.

Related questions

PostgreSQL - how to run VACUUM from code outside transaction block?

I am using Python with psycopg2 and I'm trying to run a full VACUUM after a daily operation which inserts several thousand rows. The problem is that when I try to run the VACUUM command within my code I get …

Read More
Vacuum tables in postgresql

Using postgresql-9.2,at this moment am finding the tables that have dead_tuples using the following query select relname from pg_stat_user_tables where n_dead_tup > 0 this will retuns the table name and then I'll run VACUUM …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More