Re: best way to kill long running query? - Mailing list pgsql-general

Bill/Magnus/Tom

No query should be running on ad inifinitum
Take a look at
http://euler.slu.edu/~goldwasser/courses/slu/csa341/2003_Fall/lectures/oracle_optimizer/#optimizer
99% of the queries I see I can optimise by application of these simple rules
do an explain plan
understand everything that this tells you
Make the appropriate adjustments (even it involves building an index)

Take some advice from the undisputed king of oracle optimisation
http://www.dba-oracle.com/articles.htm

Keep me apprised,
Martin--
--------------------------------------------------------------------------- 
This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is
addressedand may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you
arenot the intended recipient, you are notified that any dissemination, distribution or copying of this communication
isstrictly prohibited.
 
--------------------------------------------------------------------------- 
Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire
indiquéet peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce
document,nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire.
 
----- Original Message ----- 
From: "Bill Eaton" <EE2@aeroantenna.com>
To: "Magnus Hagander" <magnus@hagander.net>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; <pgsql-general@postgresql.org>
Sent: Wednesday, March 21, 2007 4:04 PM
Subject: Re: [GENERAL] best way to kill long running query?


>>>> I want to allow some queries for my users to run for a
>>>> prescribed period of time and kill them if they go over
>>>> time. Is there a good way to do this?
> 
>>> set statement_timeout perhaps?
>> I don't think you can set GUC parameters from the ODBC driver. Your
>> options are:
>>
>> * postgresql.conf. Will apply to all sessions to the databse.
>>
>> * database. Use ALTER DATABLASE foo SET statement_timeout=<something>.
>> Will then affect all connections to that database.
>>
>> * user. Use ALTER ROLE foo SET statement_timeout=<something>. Will then
>> affect all connections from that user.
>>
>> * change your application to issue a "SET statement_timeout=<something>"
>> query before anything else it sends.
>>
> 
> The last option is almost exactly what I wanted. It works quite nicely The
> only downside to this approach is that an error is raised if the timeout is
> exceeded. Which, when I think about it, is probably good behavior --> it
> allows me to distinguish between (1) a query that completed and returned no
> records and (2) a query that aborted because it exceeded the timeout.
> 
> Thanks to all for the assist.
> 
> Bill Eaton
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>