From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Viktor Leis <leis(at)in(dot)tum(dot)de>, Craig Ringer <craig(at)2ndquadrant(dot)com>, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Experimental evaluation of PostgreSQL's query optimizer |
Date: | 2015-12-22 16:10:56 |
Message-ID: | 14458.1450800656@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> From my point of view, one interesting fact about database
> optimization is that the numbers 0 and 1 are phenomenally important
> special cases.
Yeah.
> It is often the case that a join will return at most 1
> row per outer row, or that an aggregate will generate exactly 1 group,
> or whatever. And the code is littered with special cases - including
> Nested Loop - that cater to making such cases fast. Those cases arise
> frequently because people engineer their data so that they occur
> frequently.
> If we could bias the planner against picking nested loops in cases
> where they will figure to win only a little but might conceivably lose
> a lot, that would probably be a good idea. But it's not obvious
> exactly how to figure that out.
There was discussion awhile ago of trying to teach the planner to generate
rowcount estimates of 0 or 1 row only in cases where that was provably the
case, eg because the query selects on a unique key. In any other
situation, rowcount estimates would be clamped to a minimum of 2 rows.
This should by itself eliminate the worst abuses of nestloop plans, since
the planner would always assume that the outer scan contains at least 2
rows unless it's known not to. Still, there might be a lot of other less
pleasant side effects; it's hard to tell in advance of actually doing the
work.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Yury Zhuravlev | 2015-12-22 16:11:40 | Re: Some questions about the array. |
Previous Message | Robert Haas | 2015-12-22 15:59:22 | Re: Experimental evaluation of PostgreSQL's query optimizer |