X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 3C82A52C27
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  3 Aug 2005 06:02:43 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 76751-05
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  3 Aug 2005 09:02:33 +0000 (GMT)
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by svr1.postgresql.org (Postfix) with ESMTP id 6B01052ABA
	for <pgsql-performance@postgresql.org>;
	Wed,  3 Aug 2005 06:02:32 -0300 (ADT)
Received: from mainbox.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP
	id 5A2E140E099; Wed,  3 Aug 2005 10:02:29 +0100 (BST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP id 3028515EDA;
	Wed,  3 Aug 2005 09:53:33 +0100 (BST)
Received: from mainbox.archonet.com ([127.0.0.1])
	by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id 10207-08; Wed, 3 Aug 2005 09:53:29 +0100 (BST)
Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP id A281715ED9;
	Wed,  3 Aug 2005 09:53:29 +0100 (BST)
Message-ID: <42F08609.5060709@archonet.com>
Date: Wed, 03 Aug 2005 09:53:29 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Tobias Brox <tobias@nordicbet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: "nice"/low priority Query
References: <20050802160434.GD9278@tobias.lan>
	<20050802195915.GA9691@tobias.lan>
In-Reply-To: <20050802195915.GA9691@tobias.lan>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.057 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200508/26
X-Sequence-Number: 13773

Tobias Brox wrote:
> [Tobias Brox - Tue at 06:04:34PM +0200]
> 
>>(...) and at one
>>point in the code I'm even asking the database for "explain plan", grepping
>>out the estimated cost number, and referring the user to take contact with
>>the IT-dept if he really needs the report.  But I digress.
> 
> 
> I just came to think about some more "dirty" tricks I can do.  I have turned
> on stats collection in the configuration; now, if I do:
> 
>   select count(*) from pg_stat_activity where not current_query like '<IDLE>%';
>   
> or, eventually:
> 
>   select count(*) from pg_stat_activity 
>   where not current_query like '<IDLE>%' and query_start+'1 second'<now();
> 
> it will give a hint about how busy the database server is, thus I can
> eventually let the application sleep and retry if there are any other heavy
> queries in progress.

Or - create a table with an estimated_cost column, when you start a new 
"heavy" query, insert that query's cost, then sleep 
SUM(estimated_cost)/100 secs or something. When the query ends, delete 
the cost-row.

Hmm - actually rather than dividing by 100, perhaps make it a tunable value.
--
   Richard Huxton
   Archonet Ltd