> I've inherited a web service on a Windows server that connects to > a Postgres database. There is a "SELECT * FROM" query below that > runs a long time and appears to happen whenever there is a web > service update. I'd like to eliminate or limit it but I have to > find it first.
The first thing I would look at is whether the software stack on your web server includes some sort of table cache which is being invalidated by each update.
I have not had access to the web server yet, the owner works on it with the developer but knowledge is limited there and this was setup years ago. I can see if I can get access or ask the developer but this might not happen. I'm told it might be using ODBC to connect, I'm not familiar with Windows, I'm a linux admin. I'll look up table caching and see if we can find it there.
Thanks for your help,
Andrew
Hi Everyone,
An update. The original developer worked on this for a while. The last he communicated he thought it had to do with a count query he was sending to ODBC and even though it was limited, the older ODBC connector was performing a SELECT * query and calculating the value itself. I don't really know what he did, he made a number of changes and tests since then but the last change did stop the volume of SELECT * queries from his code. I see in pgBadger that the average select query duration for each day is around 20ms all day now instead of spiking over 50ms. We are processing phone calls and have a lot more traffic to move to this database so this is helpful.