Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 658
  • Last Modified:

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?
0
dthansen
Asked:
dthansen
  • 2
  • 2
  • 2
1 Solution
 
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
 
dthansenAuthor Commented:
I am not concerned with running VACUUM ANALYZE inside the function, just ANALYZE.

Thanks,
Dean
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
thewildCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now