setting for maximum acceptable plan cost?
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
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Fri, 02 Nov 2007 13:49:27 -0700
"Jeffrey W. Baker" <jwbaker@acm.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-----
"Jeffrey W. Baker" <jwbaker@acm.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
On Fri, 2007-11-02 at 14:45 -0700, Joshua D. Drake wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1On Fri, 02 Nov 2007 13:49:27 -0700
"Jeffrey W. Baker" <jwbaker@acm.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