asked on

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?

earth man2

No
Analyze the SQL statements that are causing your bottlneck individually standalone.

thewild

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.

dthansen

ASKER

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

Thanks,
Dean

ASKER CERTIFIED SOLUTION

thewild

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dthansen

ASKER

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

earth man2

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