Re: Mystery SELECT * query

On Mon, Dec 9, 2013 at 7:08 PM, Andrew Puschak <apuschak(at)gmail(dot)com> wrote:

> On Mon, Dec 9, 2013 at 6:57 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>
>> Andrew Puschak <apuschak(at)gmail(dot)com> wrote:
>>
>> > 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.
>>
>> --
>> Kevin Grittner
>> EDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>
> Hi Kevin,
>
> 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.

Thanks for your help,
Andrew