Re: Heavy queries not run by user application

On 02/12/11 5:11 PM, Ruben Blanco wrote:
> Hi:
>
> I'm running a Postgres database with a total disk occupation of 100Gb,
> largest and most used table up to 40Gb (about 30.000.000 tuples).
>
> Overall performance degrades sometimes due to some queries that are
> not run by the final user app. I guess they are run by Postgres
> itself. They use to take up to 100% of CPU and delay user queries
> substantially.
>
> From 'pg_stat_activity', you can see this pattern in "current_query"
> column for these queries:
>
> SELECT * FROM "public"."tablename" ORDER BY "column1",
> "column2"... LIMIT 1000 OFFSET 144000
>
> Sometimes with 'SET DATESTYLE = "ISO"'; before the SELECT.
>

there's nothing like that run by postgres itself automagically, it must
be some software at your end you're not aware of.

in pg_stat_activity, check usename, client_addr and client_port, and
match this up against netstat or whatever activity to determine what
application is making these queries.