Lists: | pgsql-general |
---|
From: | "Jeffrey W(dot) Baker" <jwbaker(at)acm(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | setting for maximum acceptable plan cost? |
Date: | 2007-11-02 20:49:27 |
Message-ID: | 1194036567.11267.19.camel@squeak |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
I was thinking that it might be nice to be able to tell postgres to
refuse to execute any plan with an estimated cost above some threshold.
For example, earlier today I produced this extremely bogus execution
plan with the following top line:
Nested Loop Left Join (cost=13920.16..2257575559347.46 rows=3691992705807 width=128)
After a call to ANALYZE, the same query gave me:
Merge Left Join (cost=16382.02..16853.87 rows=126768 width=59)
And runs in 5 seconds. If I had been able to tell pg to reject any plan
with cost over, say 10E9, that would have saved my server from half an
hour of nested sequential scans.
Should I just use statement_timeout as a proxy for this?
-jwb
From: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
---|---|
To: | "Jeffrey W(dot) Baker" <jwbaker(at)acm(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: setting for maximum acceptable plan cost? |
Date: | 2007-11-02 21:45:00 |
Message-ID: | 20071102144500.5f543602@scratch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Fri, 02 Nov 2007 13:49:27 -0700
"Jeffrey W. Baker" <jwbaker(at)acm(dot)org> wrote:
> Nested Loop Left Join (cost=13920.16..2257575559347.46
> rows=3691992705807 width=128)
>
> After a call to ANALYZE, the same query gave me:
>
> Merge Left Join (cost=16382.02..16853.87 rows=126768 width=59)
>
> And runs in 5 seconds. If I had been able to tell pg to reject any
> plan with cost over, say 10E9, that would have saved my server from
> half an hour of nested sequential scans.
I am confused as to why you would want to do that... seems like a
band aid for lack of maintenance.
>
> Should I just use statement_timeout as a proxy for this?
>
That would yes but see my point about maintenance above.
Joshua D. Drake
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHK5peATb/zqfZUUQRAttyAJ9pHjQUuyY7e2cJXtkB2239vOqAxACfX2XW
AHVlhc4g/mzc7uesWpAGls0=
=i6n+
-----END PGP SIGNATURE-----
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Jeffrey W(dot) Baker" <jwbaker(at)acm(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: setting for maximum acceptable plan cost? |
Date: | 2007-11-02 21:47:57 |
Message-ID: | 28423.1194040077@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Jeffrey W. Baker" <jwbaker(at)acm(dot)org> writes:
> I was thinking that it might be nice to be able to tell postgres to
> refuse to execute any plan with an estimated cost above some threshold.
What you suggest has been suggested before, and I might think it was
a good idea if I trusted the planner's cost estimates more ;-)
There's always statement_timeout.
regards, tom lane
From: | "Jeffrey W(dot) Baker" <jwbaker(at)acm(dot)org> |
---|---|
To: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: setting for maximum acceptable plan cost? |
Date: | 2007-11-02 21:53:09 |
Message-ID: | 1194040389.13218.1.camel@squeak |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Fri, 2007-11-02 at 14:45 -0700, Joshua D. Drake wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Fri, 02 Nov 2007 13:49:27 -0700
> "Jeffrey W. Baker" <jwbaker(at)acm(dot)org> wrote:
>
> > Nested Loop Left Join (cost=13920.16..2257575559347.46
> > rows=3691992705807 width=128)
> >
> > After a call to ANALYZE, the same query gave me:
> >
> > Merge Left Join (cost=16382.02..16853.87 rows=126768 width=59)
> >
> > And runs in 5 seconds. If I had been able to tell pg to reject any
> > plan with cost over, say 10E9, that would have saved my server from
> > half an hour of nested sequential scans.
>
> I am confused as to why you would want to do that... seems like a
> band aid for lack of maintenance.
Well it's not "maintenance" really since all the inputs are temp tables,
but I do see your point.
Often I have wished for a language which is not SQL which would allow me
to simply specify the whole execution plan. That would cut out a lot of
ambiguity.
Pie in the sky, I know.
-jwb