Thread: Cost limit.

Cost limit.

From
Shaun Thomas
Date:
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              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+



Re: Cost limit.

From
Bruce Momjian
Date:
> 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

Re: Cost limit.

From
Tom Lane
Date:
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

Re: Cost limit.

From
Shaun Thomas
Date:
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              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+



Re: Cost limit.

From
Marek Pętlicki
Date:
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>


Re: Cost limit.

From
Shaun Thomas
Date:
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              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+



Re: Cost limit.

From
Tom Lane
Date:
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

Re: Cost limit.

From
Bruce Momjian
Date:
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