| Lists: | pgsql-performance |
|---|
| From: | Bruce Momjian <bruce(at)momjian(dot)us> |
|---|---|
| To: | "Jonah H(dot) Harris" <jharris(at)enterprisedb(dot)com> |
| Cc: | josh(at)agliodbs(dot)com, pgsql-performance(at)postgresql(dot)org, Denis Lussier <denisl(at)enterprisedb(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "korryd(at)enterprisedb(dot)com" <korryd(at)enterprisedb(dot)com>, "Craig A(dot) James" <cjames(at)modgraph-usa(dot)com> |
| Subject: | Re: Simple join optimized badly? |
| Date: | 2006-10-09 02:12:12 |
| Message-ID: | 200610090212.k992CCP10515@momjian.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
Jonah H. Harris wrote:
> On Oct 08, 2006 07:05 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> > Hints are used because the DBA thinks that they are smarter than
> > the optimizer; 99% of the time, they are wrong.
>
> That's a figure which I'm 100% sure cannot be backed up by fact.
>
> > Just try manually optimizing a complex query, you'll see -- with three
> > join types, several scan types, aggregates, bitmaps, [blah blah blah]
> > it's significantly more than a human can figure out accurately.
>
> Let me get this right... the optimizer is written by humans who know and
> can calculate the proper query plan and generate code to do the same;
> yet humans aren't smart enough to optimize the queries themselves? A bit
> of circular reasoning here?
I can do 100! on my computer, but can't do it in my head.
--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
| From: | "Craig A(dot) James" <cjames(at)modgraph-usa(dot)com> |
|---|---|
| To: | Bruce Momjian <bruce(at)momjian(dot)us> |
| Cc: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Simple join optimized badly? |
| Date: | 2006-10-09 02:42:44 |
| Message-ID: | 4529B724.9070904@modgraph-usa.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
Bruce Momjian wrote:
> I can do 100! on my computer, but can't do it in my head.
A poor example. 100! is a simple repetative calculation, something computers are very good at. Optimizing an SQL query is very difficult, and a completely different class of problem.
The fact is the PG team has done a remarkable job with the optimizer so far. I'm usually very happy with its plans. But humans still beat computers at many tasks, and there are unquestionably areas where the PG optimizer is not yet fully developed.
When the optimizer reaches its limits, and you have to get your web site running, a HINT can be invaluable.
I said something in a previous version of this topic, which I'll repeat here. The PG documentation for HINTs should be FILLED with STRONG ADMONITIONS to post the problematic queries here before resorting to hints.
There will always be fools who abuse hints. Too bad for them, but don't make the rest of us suffer for their folly.
Craig