-1

Whenever i upgrade my database schema from software(code) via multiple migrations, some database operations takes lot of time to complete. I understood pgstats that, something was messed up and query plan has got modified. The same operations will complete in 2-3 seconds after running ANALYZE manually.

Digging more into autovacuum and analyze made me understand what it does. But my requirement is to run ANALYZE after my db schema is updated.

My application is using Entity framework code first approach. The database schema updates when my software is launched and booted up. Even though my postgres autovacuum parameter is set to ON, it doesnt run when schema updates. So, I need to run ANALYZE after my software has booted up.

How can i run ANALYZE on the whole database from my code after the software has booted up and just before any clicks happen ? Its a C# WPF application.

vinmm
  • 277
  • 1
  • 3
  • 14
  • EF 6 or EF Core? Can you post your startup code? – Jeremy Lakeman Mar 13 '20 at 05:35
  • Your application shouldn't be changing the database schema on every startup and thus you shouldn't need to ANALYZE it on every startup. Can you post some code to show what you're doing? P.S try not changing anything in the database on application startup and run a query, if it is still slow the problem may stem from the cold query EF executes before the first query actually executes: https://stackoverflow.com/a/16407062/9363973 – MindSwipe Mar 13 '20 at 06:03

1 Answers1

1

You have to do it explicitly. Schema changes don't trigger an analyze, and even if it did, it would take a while to finish processing.

But it should be simple to add an ANALYZE for each affected table as part of the upgrade procedure.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263