Postgres Analyze

We have a function that truncates a table (table1), then populates it with several million rows and then inner joins to another table (table2) that also has several million rows.

Is it possible/advisable to ANALYZE table1 within the function? i.e., right after we dump the millions of rows into table1, can we issue an “ANALYZE table1” from within the function before we join it to table2?
dthansenAsked:
Who is Participating?
 
thewildConnect With a Mentor Commented:
Sorry, since you were asking whether it was possible, I thought you meant VACUUM ANALYZE.
ANALYZE is possible inside a procedure.
In your case it is probably recommended.
Have you tried to run EXPLAIN ANALYZE on the query joining your tables with and without running ANALYZE first ?
0
 
earth man2Commented:
No
Analyze the SQL statements that are causing your bottlneck individually standalone.
0
 
thewildCommented:
No, you cannot run VACUUM ANALYZE inside a function.
More generally, you cannot run VACUUM inside a transaction block. Since functions are always run in a transaction, VACUUM is not possible inside them.

Instead of a stored procedure, you could run the statements one by one.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
dthansenAuthor Commented:
I am not concerned with running VACUUM ANALYZE inside the function, just ANALYZE.

Thanks,
Dean
0
 
dthansenAuthor Commented:
One of the tables is a temporary table created function. I'm not sure it is possible to run an EXPLAIN ANALYZE on that table as it is both created and dropped within the function.

Thanks,
Dean
0
 
earth man2Commented:
create a new stored procedure that creates the data as before but does not delete the table and data.  Then interactively execute the SQL with EXPLAIN ANALYZE
0
All Courses

From novice to tech pro — start learning today.