Thread: Cost limit.
Hey all, I can't seem to find it in the docs, so I'll ask here. Is there a way in postgres to impose a limit on query costs? I'd noticed some horribly disfigured queries hitting my poor database from one of our developers, and corrected him. But many of our users are not so easily contacted. What I want to know is if there's a configuration parameter or source patch that will allow me to disallow a query execution if the pre-execution cost estimation is too high. The reason I'm asking? Our developer accidentally created a cross product that resulted in an explain-plan cost of approximately 1.3 trillion. Before we tracked the problem down, the application causing the query would halt for an answer indefinitely. Even after stopping the application, postgres would grind on. The load on the machine was hovering around 20, with 5 postgres threads sharing the blame. I'd hate for any user to have the power to cripple our database server by simply writing a bad query. Should I contact the developers list on this and request a patch? Could someone direct me to the correct piece of source code to... er... enhance? Thanks in advance. -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. Thomas INN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830 | | Email: sthomas@townnews.com AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
> Actually I was thinking of something to cut the query off at the pass. I > looked through the code, and it does a query plan and uses the one with > least cost. So, theoretically, I should be able to hack the code so that > it will refuse to execute a query if the best query plan has an estimated > cost of over 100,000 for example. > > Some kind of error like: "Query not executed due to detection of possible > cross product." > > Of course, I'd like to stay away from source hacks, besides It would be > nice to tune from postgresql.conf as a parameter like max_query_cost. > > The source is a bit spaghetti-ish, and I'm having a hard time following > some of the macros, typedefs and structs. I don't think I could add > a feature like this myself. I don't think our optimizer estimates are that accurate. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Shaun Thomas <sthomas@townnews.com> writes:
> I can't seem to find it in the docs, so I'll ask here. Is there a
> way in postgres to impose a limit on query costs? I'd noticed some
> horribly disfigured queries hitting my poor database from one of our
> developers, and corrected him. But many of our users are not so
> easily contacted. What I want to know is if there's a configuration
> parameter or source patch that will allow me to disallow a query
> execution if the pre-execution cost estimation is too high.
Given the inherent inaccuracy of the cost estimates, I'd be real
hesitant to rely on them to suppress overly-expensive queries.
What might make sense is a time limit on execution (after x amount
of time, give up and cancel the query). No one's put such a thing
into the backend AFAIK. Note you could implement such a time limit
purely on the client side, which might be an easier and more flexible
way to go.
regards, tom lane
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. -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. Thomas INN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830 | | Email: sthomas@townnews.com AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
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>
On Fri, 18 May 2001, Bruce Momjian wrote: > That is on the TODO list: > > * Add SET or BEGIN timeout parameter to cancel query if waiting > too long Actually I was thinking of something to cut the query off at the pass. I looked through the code, and it does a query plan and uses the one with least cost. So, theoretically, I should be able to hack the code so that it will refuse to execute a query if the best query plan has an estimated cost of over 100,000 for example. Some kind of error like: "Query not executed due to detection of possible cross product." Of course, I'd like to stay away from source hacks, besides It would be nice to tune from postgresql.conf as a parameter like max_query_cost. The source is a bit spaghetti-ish, and I'm having a hard time following some of the macros, typedefs and structs. I don't think I could add a feature like this myself. -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. Thomas INN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830 | | Email: sthomas@townnews.com AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
Shaun Thomas <sthomas@townnews.com> writes:
>> 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.
Sounds like PHP is not actually trying to cancel the query when it
gives up waiting. Anyone care to go in and fix that?
regards, tom lane
That is on the TODO list:
* Add SET or BEGIN timeout parameter to cancel query if waiting
too long
> Hey all,
>
> I can't seem to find it in the docs, so I'll ask here. Is there a
> way in postgres to impose a limit on query costs? I'd noticed some
> horribly disfigured queries hitting my poor database from one of our
> developers, and corrected him. But many of our users are not so
> easily contacted. What I want to know is if there's a configuration
> parameter or source patch that will allow me to disallow a query
> execution if the pre-execution cost estimation is too high.
>
> The reason I'm asking? Our developer accidentally created a cross
> product that resulted in an explain-plan cost of approximately
> 1.3 trillion. Before we tracked the problem down, the application
> causing the query would halt for an answer indefinitely. Even after
> stopping the application, postgres would grind on. The load on the
> machine was hovering around 20, with 5 postgres threads sharing
> the blame. I'd hate for any user to have the power to cripple
> our database server by simply writing a bad query.
>
> Should I contact the developers list on this and request a patch?
> Could someone direct me to the correct piece of source code to...
> er... enhance?
>
> Thanks in advance.
>
> --
> +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
> | Shaun M. Thomas INN Database Programmer |
> | Phone: (309) 743-0812 Fax : (309) 743-0830 |
> | Email: sthomas@townnews.com AIM : trifthen |
> | Web : hamster.lee.net |
> | |
> | "Most of our lives are about proving something, either to |
> | ourselves or to someone else." |
> | -- Anonymous |
> +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026