On Friday, May, 2001-05-18 at 20:42:57, Shaun Thomas wrote:
> On Fri, 18 May 2001, Tom Lane wrote:
>
> > Given the inherent inaccuracy of the cost estimates, I'd be real
> > hesitant to rely on them to suppress overly-expensive queries.
>
> That's just it. You don't have to be that accurate. If an estimate
> goes into the millions or billions, something is obviously wrong with
> the query, period. I'd just like to set a cutoff.
>
> > What might make sense is a time limit on execution (after x amount
> > of time, give up and cancel the query).
>
> That would work, but during that time the query is executing, that
> process is eating up cpu power and stealing resources from valid
> queries, possibly making them run slower, thus causing a chain of
> queries that could eventually time out as well.
>
> > Note you could implement such a time limit purely on the client side,
> > which might be an easier and more flexible way to go.
>
> That's just it. PHP (our front end) has a 30 second time limit we
> have not overridden. But we've found that after the PHP execution stops
> in disgust, the postgres thread keeps going until we restart the
> postgres back end. That is simply unacceptable. Especially
> considering any user on our system could feasibly (accidently or
> maliciously) take our database server to a crawl by writing a
> single bad query that may take days to track down.
I think that some interface to 'explain' returning a tuple of its values
could serve the purpose. You could first run the explain and then, when
the results are satisfiable run the query. It would need, off course, to
prepare some additional layer to test the estimations results, but could
prove usefull.
I'm not sure, that such an interface currently exists (it could be a little
tricky given the tree-form of 'explain' result) but I would benefit that
kind of tool myself...
regards
--
Marek Pętlicki <marpet@buy.pl>